selecting sheets in VBA

gfalc3194

New Member
Joined
Jun 2, 2002
Messages
13
If I select the first sheet in a workbook, hold shift and go to the
last sheet, all are selected for printing. However, when I record this
function, the VBA code lists all sheets by name.

For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3'
the macro code recorded = Sheets(Array("sheet1", "sheet2",
"sheet3")).Select

I would like to know if there is a way to select from sheet1 to sheet3
without explicitly naming them. The problem is that in the actual
workbook, sometimes I add or delete sheets, and sometimes rename them
for clarity. I don't want to have to modify the macro everytime I do
that. (the first and last sheets can always remain the same).
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Just curious, why don't you send the entire workbook for print instead of selecting the sheets first ?

ActiveWorkbook.PrintOut

but, if you want to select the sheets anyway, you can use their index number:

Sheets(Array(1,2)).Select

to select the first two.
 
Upvote 0
I guess I forgot to mention that sheet1 through sheet3 are not the only sheets in the workbook. Its more like this:
Sheet1, sheet2.....up to sheet6.
I first want to print sheet1 through sheet3, then print sheet4 through sheet6. So I can't just print all the sheets in the workbook. Likewise, I can't just say, select sheets 1,2,3, because at sometime I may add sheet1b. Now I have four sheets to print and would have to change the macro.
 
Upvote 0
You can use the Codenames instead of the Names. They are unlikely to change.

Maybe this will help you:

Code:
Sub Test()
    Dim i As Integer
    Dim x As Integer
    i = Sheet3.Index
    Dim Arr() As String
    ReDim Preserve Arr(1 To i)
    For x = 1 To Sheet3.Index
        Arr(x) = Worksheets(x).Name
    Next x
    Sheets(Arr).Select
End Sub
 
Upvote 0
Andrew,

I duplicated your code exactly and just changed the name of sheet3 to the actual name. Two things: If the sheet name has spaces in it, I get a compile erroe, "expected end of statement". I changed the name to one with no spaces but then got a 424 error, "object required".

Any ideas? Thanks
 
Upvote 0
Hi g,

Your response to Andrew suggests that you have misunderstood his suggestion (either that or you've found a way to include illegal characters in the CodeName :wink: ).

Try this - activate the VBE (Alt & F11) and look at the Project window (Ctrl & R if its not visible). For the workbook in question you will see the sheets listed. A new workbook would show as the first sheet : Sheet1(Sheet1). The first 'Sheet1' is the CodeName and the second one is the worksheet tab name.

Now, you can easily change the tab name (and include spaces) but the CodeName can only be changed in the VBE (it can be changed via code but we won't get into that right now!). It is for this reason that it is often useful to use the CodeName of a sheet in your VBA routines. For example, instead of referring to the sheet in the above example as 'Worksheets("Sheet1")' you would simply use 'Sheet1'.

So, in Andrew's example the 'Sheet3' was the CodeName and you should not amend it unless you have actually changed the CodeName.

HTH
 
Upvote 0
Ok I'm still confused. I think using sheet names of sheet1 etc is what's doing it.

The actual workbook contains 42 sheets. The 1st 20 is output, the next 22 is backup. Sometimes I want to print the 1st 20, numbered 1 - 20, and the backup, numbered 1 -22. Sometimes I just print the first 20 output sheets.

So as not to confuse myself any more than I already am, lets say the sheets are called One, Two, Three, Four, .....Twenty. Backup One, Backup Two.....Backup Twenty-two.

using codenames, would the the formula be i = Sheet20.index? In other words, are sheet1, sheet2, etc codenames for the placement of the sheet in the workbook? When I print backup, whould the codename be sheet 42, and the code would be "ReDim Preserve Arr(21 to i)"?

I used a test workbook with 2 sheets in it, used i = sheet2, and it worked.
When I tried the actual workbook, using i = sheet20, when I got to the Sheets(Arr).select statement, I got an error "Select method of sheets class failed". Also, for testing, I changed sheet20 to sheet3, hoping to just select 3 sheets. The first time, from stepping into the code, it seemed to select 20 sheets (how did it know to stop at 20???). A subsequent run gave me a subscript out of range error.

I'm lost. Possibly hopeless.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top