Error 1004 using array in Sheet select.

T

Trefor

If I select a sheet with its real name:

Sheets("Sheet1").Select >>>>>- Works fine
Sheets(Array("Sheet1", "Sheet2")).Select >>>>>- Works fine

If I assign the sheet name(s) to a constant

Public Const TSGSheet1 = "Sheet1"
Public Const TSGSheet2 = "Sheet2"

Sheets(TSGSheet1).Select >>>>>- Works fine
Sheets(TSGSheet2).Select >>>>>- Works fine

Sheets(Array(TSGSheet1, TSGSheet2)).Select >>>>>- fails with Error 1004

My macro's/VBA do multiple Selects and rather than explicitly specifying the
sheet names in every command, I thought I would set up a constant, use the
constant in the multiple commands. If I then wanted to change the sheet name,
I could just change the constant assignment once.

Have I done something wrong? It would appear that you can not use constants
in an array?
 
J

Jim Cone

Trefor,

It works for me and selects the two sheets,
if the public constants are placed at the top of the module above all the subs.

Jim Cone
San Francisco, USA


"Trefor" <[email protected]>
wrote in message
If I select a sheet with its real name:

Sheets("Sheet1").Select >>>>>- Works fine
Sheets(Array("Sheet1", "Sheet2")).Select >>>>>- Works fine

If I assign the sheet name(s) to a constant
Public Const TSGSheet1 = "Sheet1"
Public Const TSGSheet2 = "Sheet2"
Sheets(TSGSheet1).Select >>>>>- Works fine
Sheets(TSGSheet2).Select >>>>>- Works fine
Sheets(Array(TSGSheet1, TSGSheet2)).Select >>>>>- fails with Error 1004

My macro's/VBA do multiple Selects and rather than explicitly specifying the
sheet names in every command, I thought I would set up a constant, use the
constant in the multiple commands. If I then wanted to change the sheet name,
I could just change the constant assignment once.
Have I done something wrong? It would appear that you can not use constants
in an array?
 
D

Dave Peterson

And it worked for me, too.

Are you sure that the active workbook had sheets with those two names?
 
T

Trefor

Sorry misled you a bit.

Try the following (same constants as before)

Sheets(TSGSheet1).Visible = True
Sheets(TSGSheet2).Visible = True

Sheets(Array(TSGSheet1, TSGSheet2)).Visible = False

Sheets(Array(TSGSheet1, TSGSheet2)).Visible = True <<<<< This fails

The line in my original question failed because one sheet was hidden. The
last line above fails with the same error.
 
D

Dave Peterson

Yep.

I think you'll want to do one sheet at a time.
Sorry misled you a bit.

Try the following (same constants as before)

Sheets(TSGSheet1).Visible = True
Sheets(TSGSheet2).Visible = True

Sheets(Array(TSGSheet1, TSGSheet2)).Visible = False

Sheets(Array(TSGSheet1, TSGSheet2)).Visible = True <<<<< This fails

The line in my original question failed because one sheet was hidden. The
last line above fails with the same error.
Click to expand...
 
Top