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 apprci...
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

     

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
mdevola89's avatar
mdevola89
Copper Contributor
Dec 13, 2022
Tried this, the macro still doesn't run.
  • 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
    • mdevola89's avatar
      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's avatar
        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

         

Resources