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.

Resources