Passing Selected Sheets' Names to a Macro
 

I want to write a print macro that will print sheets based on a
selection of sheets that the user makes. To make things
user-friendly, I want the process to be: 1) user highlights one or
more sheets from the workbook, 2) user presses Ctrl+Shift+P to launch
a print macro and 3) the printing starts. This is easy enough to use:

ActiveWindow.SelectedSheets.PrintOut

But I need to do things on the selected sheets first, such as doing a
Data AutoFilter to hide rows on the sheets, so I need to go to each
one of the selected sheets (let's say they are Sheet1, Sheet4 and
Sheet5) and do the Autofilter on each sheet before printing them out
(Autofilter does not work across grouped sheets). So my problem is:
how do I get the information on the selected sheets' names to that I
can go to each one of them and do Autofilter steps?

I'd also still like to be able to print them out as a group so that
the pagination numbering also works.

I've been scratching my head on this! Can this be done? Any help
would be appreciated.

Passing Selected Sheets' Names to a Macro
 

Sub Tester3()

Dim shts As Sheets
Dim sh As Worksheet
Set shts = ActiveWindow.SelectedSheets
' ungroup sheets
ActiveSheet.Select
For Each sh In shts
' do what you need to do
' just to show that the sheets are ungrouped:
sh.Activate
MsgBox "continue"
Next
' Regroup sheets
shts.Select
End Sub

--
Regards,
Tom Ogilvy


"John Tjia" wrote in message
om...
I want to write a print macro that will print sheets based on a
selection of sheets that the user makes. To make things
user-friendly, I want the process to be: 1) user highlights one or
more sheets from the workbook, 2) user presses Ctrl+Shift+P to launch
a print macro and 3) the printing starts. This is easy enough to use:

ActiveWindow.SelectedSheets.PrintOut

But I need to do things on the selected sheets first, such as doing a
Data AutoFilter to hide rows on the sheets, so I need to go to each
one of the selected sheets (let's say they are Sheet1, Sheet4 and
Sheet5) and do the Autofilter on each sheet before printing them out
(Autofilter does not work across grouped sheets). So my problem is:
how do I get the information on the selected sheets' names to that I
can go to each one of them and do Autofilter steps?

I'd also still like to be able to print them out as a group so that
the pagination numbering also works.

I've been scratching my head on this! Can this be done? Any help
would be appreciated.

Passing Selected Sheets' Names to a Macro
 

I found this in the archives. You can modify to suit.
http://tinyurl.com/3fomx

--
Don Guillett
SalesAid Software

"John Tjia" wrote in message
om...
I want to write a print macro that will print sheets based on a
selection of sheets that the user makes. To make things
user-friendly, I want the process to be: 1) user highlights one or
more sheets from the workbook, 2) user presses Ctrl+Shift+P to launch
a print macro and 3) the printing starts. This is easy enough to use:

ActiveWindow.SelectedSheets.PrintOut

But I need to do things on the selected sheets first, such as doing a
Data AutoFilter to hide rows on the sheets, so I need to go to each
one of the selected sheets (let's say they are Sheet1, Sheet4 and
Sheet5) and do the Autofilter on each sheet before printing them out
(Autofilter does not work across grouped sheets). So my problem is:
how do I get the information on the selected sheets' names to that I
can go to each one of them and do Autofilter steps?

I'd also still like to be able to print them out as a group so that
the pagination numbering also works.

I've been scratching my head on this! Can this be done? Any help
would be appreciated.

Passing Selected Sheets' Names to a Macro
 

This should even be easier
Sub doselectedworksheets()
For Each ws In ActiveWindow.SelectedSheets
ws.[a1] = 1
'your code here
Next
End Sub

--
Don Guillett
SalesAid Software

"John Tjia" wrote in message
om...
I want to write a print macro that will print sheets based on a
selection of sheets that the user makes. To make things
user-friendly, I want the process to be: 1) user highlights one or
more sheets from the workbook, 2) user presses Ctrl+Shift+P to launch
a print macro and 3) the printing starts. This is easy enough to use:

ActiveWindow.SelectedSheets.PrintOut

But I need to do things on the selected sheets first, such as doing a
Data AutoFilter to hide rows on the sheets, so I need to go to each
one of the selected sheets (let's say they are Sheet1, Sheet4 and
Sheet5) and do the Autofilter on each sheet before printing them out
(Autofilter does not work across grouped sheets). So my problem is:
how do I get the information on the selected sheets' names to that I
can go to each one of them and do Autofilter steps?

I'd also still like to be able to print them out as a group so that
the pagination numbering also works.

I've been scratching my head on this! Can this be done? Any help
would be appreciated.

Passing Selected Sheets' Names to a Macro
 

Looks like Sparky ripped off John Walkenbach without providing credit: So
sad.

http://j-walk.com/ss/excel/tips/tip48.htm

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
I found this in the archives. You can modify to suit.
http://tinyurl.com/3fomx

--
Don Guillett
SalesAid Software

"John Tjia" wrote in message
om...
I want to write a print macro that will print sheets based on a
selection of sheets that the user makes. To make things
user-friendly, I want the process to be: 1) user highlights one or
more sheets from the workbook, 2) user presses Ctrl+Shift+P to launch
a print macro and 3) the printing starts. This is easy enough to use:

ActiveWindow.SelectedSheets.PrintOut

But I need to do things on the selected sheets first, such as doing a
Data AutoFilter to hide rows on the sheets, so I need to go to each
one of the selected sheets (let's say they are Sheet1, Sheet4 and
Sheet5) and do the Autofilter on each sheet before printing them out
(Autofilter does not work across grouped sheets). So my problem is:
how do I get the information on the selected sheets' names to that I
can go to each one of them and do Autofilter steps?

I'd also still like to be able to print them out as a group so that
the pagination numbering also works.

I've been scratching my head on this! Can this be done? Any help
would be appreciated.

Passing Selected Sheets' Names to a Macro
 

Many thanks for the replies! Happy Holidays to all.