Forum Discussion

Copper Contributor

Dec 13, 2022

Solved

Macro to print pages

I have numerous sheets in a workbook and I'm trying to print a number of pages for each based on a value in a cell. I am not sure what I am missing to make this work. Any ideas will be greatly apprci...

Macros and VBA

HansVogelaar
Dec 14, 2022
mdevola89

The part

If Worksheets("CMM Report (" & i & ")").Visible = True Then Worksheets("CMM Report (" & i & ")").Activate

Else

Exit For ' stop the loop if a sheet is missing

End If

should be

If Worksheets("CMM Report (" & i & ")").Visible = True Then Worksheets("CMM Report (" & i & ")").Activate Else Exit For ' stop the loop if a sheet is missing End If

mdevola89

Copper Contributor

Dec 14, 2022

This didn't work either, so I tried this Code:
Sub Print_Sheets()

Dim wbkCurrent As Workbook
Set wbkCurrent = ThisWorkbook

Dim I As Integer
For I = 1 To 20
If Worksheets("CMM Report (" & I & ")").Visible = True Then Worksheets("CMM Report (" & I & ")").Activate

Else

Exit For ' stop the loop if a sheet is missing

End If

Dim PageTo As Integer

PageTo = Range("Y6").Value

With ActiveWorkbook.ActiveSheet
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=PageTo, Copies:=1
End With

Next I

End Sub
But I am now getting a Compiles error - Else without If. Not sure why as there is an If statement present.

HansVogelaar

MVP

Dec 14, 2022

mdevola89

The part

If Worksheets("CMM Report (" & i & ")").Visible = True Then Worksheets("CMM Report (" & i & ")").Activate

Else

Exit For ' stop the loop if a sheet is missing

End If

should be

        If Worksheets("CMM Report (" & i & ")").Visible = True Then
            Worksheets("CMM Report (" & i & ")").Activate
        Else
            Exit For ' stop the loop if a sheet is missing
        End If

mdevola89
Copper Contributor
Jan 03, 2023
Thank you for all your help, it was sucessful.