Selecting worksheets into groups?
 

How can I use VBA code to add worksheets to or subtract worksheets from
groups?

The problem occurs when users select several sheets for printing, when some
of the sheets are not to be printed. I would like to remove one or more
sheet from group before print dialog shows.

I'm fine with a solution making me dissolve the entire group and then
building the correct group, but cant get it to work.

Have found code that let me create a group from scratch:
This works:
Sheets(Array("Week_01", "Week_02")).Select

But this doesn't:
MyString = """ & "Week_01" & """, """ & "Week_02" & """
Sheets(Array(MyString)).Select

Conclusion this far:
Hardcoding the array works but making it flexible is hard.

Selecting worksheets into groups?
 

Hi,
try declaring a variable like this

Dim intInc as Long, intCnt as Long, Sheets4Printing() as Long

For intCnt = 1 to ThisWorkbook.Sheets.Count
'''Code to determine whether the sheet is added or not
redim Preserve Sheets4Printing(intInc)
Sheets4Printing(intInc) = Sheets(intCnt).Index
intInc = intInc + 1
Next
Sheets(Sheets4Printing).Select

This adds the sheet indexes to an array which can then be used...
Hth,
OJ

Selecting worksheets into groups?
 

Probably not significant, but just some added information, this assumes
either no option base is specified and thus the default is 0 or that option
base 0 is specified.

Dim intInc as Long, intCnt as Long, Sheets4Printing() as Long

Redim Sheets4Printing(0 to 0)
intInc = 0
For intCnt = 1 to ThisWorkbook.Sheets.Count
'''Code to determine whether the sheet is added or not
redim Preserve Sheets4Printing(0 to intInc)
Sheets4Printing(intInc) = intCnt
intInc = intInc + 1
Next
' you can go directly to printout
Sheets(Sheets4Printing).PrintOut

would eliminate that concern. Also, Sheets(intCnt).Index is redundant as
this should equal intCnt

--
Regards,
Tom Ogilvy


"OJ" wrote in message
oups.com...
Hi,
try declaring a variable like this

Dim intInc as Long, intCnt as Long, Sheets4Printing() as Long

For intCnt = 1 to ThisWorkbook.Sheets.Count
'''Code to determine whether the sheet is added or not
redim Preserve Sheets4Printing(intInc)
Sheets4Printing(intInc) = Sheets(intCnt).Index
intInc = intInc + 1
Next
Sheets(Sheets4Printing).Select

This adds the sheet indexes to an array which can then be used...
Hth,
OJ

Selecting worksheets into groups?
 

Good point Tom....you're always adjusting my posts for the better!! ;o)