Forum Discussion

mdevola89's avatar
mdevola89
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 apprciated.

 

Sub Print_Sheets()

For i = 1 To OpCount

Dim PageTo As Integer

If Sheets("CMM Report(i)").Visible = True Then

PageTo = Range("Y6").Value

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

End If

Next i

End Sub

Macros and VBA
  • HansVogelaar's avatar
    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

     

6 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 13, 2022
    I'm really not sure but on a very quick look I think this is your issue:
    If Sheets("CMM Report(i)").Visible = True Then
    the "i" needs to be outside the quotes. Try this:
    If Sheets("CMM Report(" & i & ")").Visible = True Then
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 13, 2022

        mdevola89 

        Is  defined elsewhere? If not, it will be treated as 0 so nothing will happen.

        Sub Print_Sheets()
    Dim i As Long
    Dim PageTo As Long
    For i = 1 To OpCount
        With Worksheets("CMM Report(" & i & ")")
            If .Visible Then
                PageTo = .Range("Y6").Value
                .PrintOut From:=1, To:=PageTo, Copies:=1
            End If
        End With
    Next i
End Sub

Resources