![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Board Regular
Join Date: Feb 2002
Location: Leicestershire, U K
Posts: 157
|
I have a spreadsheet that has a number of sheets but only x are visible at any one time (dependant upon selection criteria).
I wish to print only visible sheets and have constructed the following:- Sub printvisible() Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets ' Select only visible sheets and print If sh.Visible = True Then ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True End If ' go to next sheet Next sh End Sub This routine prints the same active window for the number of sheets visible and does not move to the next tab. Any help would be appreciated. |
|
|
|
|
|
#2 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Sunny, spring-like Hull
Posts: 3,339
|
Change your routine to the following, as it's only picking up the initially selected sheet and not selecting the next one: -
Sub printvisible() Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets ' Select only visible sheets and print If sh.Visible = True Then sh.Activate ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True End If ' go to next sheet Next sh End Sub [ This Message was edited by: Mudface on 2002-05-20 05:44 ] |
|
|
|
|
|
#3 |
|
New Member
Join Date: Apr 2002
Posts: 8
|
Sub printvisible()
Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets ' Select only visible sheets and print If sh.Visible = True Then sh.PrintOut copies:=1, collate:=True End If ' go to next sheet Next sh End Sub |
|
|
|
|
|
#4 |
|
Board Regular
Join Date: Feb 2002
Location: Leicestershire, U K
Posts: 157
|
Thanks,
Routine worked atreat. If I wanted to advance to the next visible sheet one sheet at a time what would I have to alter to step forward 1 ? |
|
|
|
|
|
#5 |
|
Legend
Join Date: Feb 2002
Location: Minneapolis, Mn, USA
Posts: 9,704
|
Hi Keith, you can try the following:
|
|
|
|
|
|
#6 |
|
Board Regular
Join Date: Feb 2002
Location: Leicestershire, U K
Posts: 157
|
Brilliant !
Thanks With that routine I can work out how to take it to the previous sheet. Thanks. |
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|