Jump to content
Sign in to follow this  

_ExcelSheetMove - what am I doing wrong?

Recommended Posts


Help! I am in a time bind and have 1 issue I can't seem to figure out. I am building a workbook that each tab is the day of the month [1,2,3....30, etc.] That works fine... except it creates the new sheet at the beginning of the workbook and I need it to be moved to the end so the tabs are in calerdar order. There is a "Sheet1" tab that exists at the end of the workbook and I am using that for relative position.

But the new day sheet does not move - what is wrong with my syntax?

;-------------------------------PROCESS FILE

#include <Excel.au3>

#include <Array.au3>

#include <Date.au3>

$day = @Mon&@mday

$do = @mday - 1

global $oExcel = _ExcelBookOpen("H:\Callcenter\Reports\Client External Reports\GR_Monthly_Metrics.xls")

$oExcel.Visible = True

;add a new sheet named with the Day of the Month

_ExcelSheetAddNew($oExcel, $do)

;grab the columns of data

With $oEHD.ActiveSheet.Range("A:H")



;Paste the data into Monthly xls

With $oExcel.ActiveSheet.Range("A1:A1")



;adjust the column widths

WinActivate("Microsoft Excel - GR_Metrics.xls")



$oExcel.Columns("A").ColumnWidth = 14

$oExcel.Columns("B").ColumnWidth = 14

$oExcel.Columns("C").ColumnWidth = 8

$oExcel.Columns("D").ColumnWidth = 10

$oExcel.Columns("E").ColumnWidth = 11

$oExcel.Columns("F").ColumnWidth = 7

$oExcel.Columns("G").ColumnWidth = 9

;move the new sheet to the end of the workbook BEFORE Sheet1


;get the Daily GR xls

GLobal $oEHD = _ExcelBookOpen("H:\Callcenter\Reports\Client External Reports\gr_daily_metrics.xls")

....save and close

Share this post

Link to post
Share on other sites

Well, a quick test tells me your syntax works fine.


What I'm not sure of is the string or integer issue (looks like this may be your problem):

$vRelativeSheet [optional] The moved sheet will be placed before or after this sheet (a string or integer, defaults to first sheet)

because I tested with "test" and it works fine but with the variable I get this error:

Only Object-type variables allowed in a "With" statement.: With $oEHD.ActiveSheet.Range("A:H") With ^ ERROR

I would suggest trading in your {With, copy and paste} statements for a nice sturdy array.

Hope this helps a little... Good Luck!

Edited by billo

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.