Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Automate Grouping of Sheets...

Status
Not open for further replies.

dgsbsme

Mechanical
Joined
Mar 9, 2005
Messages
39
Location
US
I need to be able to print (via VBA on a custom tool button) all sheets in a workbook, EXCEPT the first 2 sheets.

I can make it count the total number of sheets and tried to use:

Dim tabcount As Integer
tabcount = ThisWorkbook.Worksheets.Count
Worksheets(Array(2 - tabcount)).Select

Thinking that Array(2 - tabcount) would be 2 THRU tabcount... not so apparently.

Neither did Array(2 :: tabcount) or Array(2 : tabcount) work.

I'm stuck now. Anyone know how to make it do what I want?
 
2-tabcount is "2 minus tabcount"
2::tabcount means "2 (here is a new line of VBA code) tabcount"

You can't specify a range of values for the Array function. You have to specify each value. Array will not work for you. You need something like

Code:
Dim Tabcount As Long
Dim myArray() As Variant
Tabcount = ThisWorkbook.Worksheets.Count
ReDim myArray(Tabcount - 3)
For i = 0 To UBound(myArray)
  myArray(i) = i + 3
Next i
Worksheets(myArray).Select

-handleman, CSWP (The new, easy test)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top