Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Selected Sheets' Names to a Macro
Many thanks for the replies! Happy Holidays to all.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to run on selected sheets | Excel Discussion (Misc queries) | |||
How to run macro on selected sheets with one go? | Excel Discussion (Misc queries) | |||
passing values to other sheets | New Users to Excel | |||
Protecting Macro and selected sheets | Excel Discussion (Misc queries) | |||
Passing selected workbook name and values to a macro | Excel Worksheet Functions |