macro to print selected sheets

[hard worker]

hey, I need a help in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.

[Leith Ross]

Hello HardWorker,

Welcome to the Forum!

Here is macro that will loop through all the worksheets in the workbook selecting only the ones you want. The macro allows you to skip sheets. Currently the only sheet skipped is "Sheet1". You add the names of the sheets you don't selected to the code. Place the name in double quotes and separate the names with a comma.

Sub PrintSheets()

  Dim I As Long
  Dim Shts() As String
  
    For Each Sht In Sheets
      Select Case Sht.Name
        Case Is = "Sheet1"
          'Do Nothing - Skip these worksheets
        Case Else
          ReDim Preserve Shts(I)
          Shts(I) = Sht.Name
          I = I + 1
      End Select
    Next Sht
    
    Sheets(Shts).Select
    Sheets.PrintOut
    
End Sub

[hard worker]

Hey thanks,
What if I don't want to skip any sheets between my section. For example if I have 30 sheets in my excel file and I want to print consequently from sheet 1 to sheet 25.

[Leith Ross]

Hello hard worker,

This code will print sheets 1 to 25...

Sub PrintSheets()

  Dim I As Long
  Dim Shts() As String
  
    For I = 1 to 25
      ReDim Preserve Shts(I)
      Shts(I - 1) = Sht.Name
    Next Sht
    
    Sheets(Shts).Select
    Sheets.PrintOut
    
End Sub

[hard worker]

Both macro are not working. The one which suppose to skip "sheet 1" is not skipping that sheet and printing all sheets. And the one which suppose to print pages 1 to 25 is giving a error. It says "invalid next control variable reference".

[Leith Ross]

Hello hardworker,

Here is the corrected code for the first 25 sheets. I forgot the index variable for the sheet name.

Sub PrintSheets()

  Dim I As Long
  Dim Shts() As String
  
    For I = 1 to 25
      ReDim Preserve Shts(I)
      Shts(I - 1) = Shts(I).Name
    Next Sht
    
    Sheets(Shts).Select
    Sheets.PrintOut
    
End Sub

[arlu1201]

eahmed,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[arlu1201]

KEMelton,

As i said to eahmed above,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.