Error 1004 using array in Sheet select.

[Guest]

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?

 

[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?

 

[Dave Peterson]

And it worked for me, too.

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

 

[Guest]

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.

 

[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.