macro will not move to next tab

[keithkemble]

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.

[Mudface]

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 ]

[ldry]

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

[keithkemble]

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 ?

[NateO]

Hi Keith, you can try the following:

Sub nxt()

Dim y As Worksheet

On Error GoTo 1 'In case you're on the last sheet

Set y = ActiveSheet.Next

ActiveSheet.Next.Activate

ActiveSheet.PrintOut copies:=1, collate:=True

1: End Sub

[keithkemble]

Brilliant !
Thanks
With that routine I can work out how to take it to the previous sheet.

Thanks.