A VBA code to print all sheets except sheet1and sheet2

[seragrefaat]

I want to put a command button on sheet named Data and provide it with code that print other sheets on the workbook except sheet Data and sheet next to it named Template. Thanks
I found this code here in the forum, it prints only one sheet other than the sheet which has the command button, also it hides the sheet after printing, i don't want that.

Private Sub CommandButton2_Click()

Dim MyRange As Range
Dim ws As Worksheet
Dim Lastrow As Long
Dim i As Long
Dim n As Long
Dim j As Long
Dim PrintArea As String
Application.ScreenUpdating = False
Set ws = ActiveSheet
j = ActiveSheet.Index
Sheets(j + 1).Visible = True
Sheets(j + 1).Select
Debug.Print ws.Range("A1").Value
For i = 1 To 30840
If Cells(34 * i + 8, 1).Value <> "" Then
n = i + 1
Else
GoTo Printing
End If
Next i

Printing:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Print" & (j + 1) / 2 _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

Sheets(j + 1).Visible = False
Sheets(j).Select
Application.ScreenUpdating = True

End Sub

 

[seragrefaat]

It means the code is ignoring the sheets called Template and Data when it runs the loop and so there is no reason why it would printout those 2 sheets in the code below.

Sub PrintMe()
    Application.ScreenUpdating = False
    Dim Wks As Worksheet

    For Each Wks In ActiveWorkbook.Worksheets
        If Trim(LCase(Wks.Name)) <> "data" And Trim(LCase(Wks.Name)) <> "template" Then
           Wks.PrintOut
        End If
    Next Wks
    Application.ScreenUpdating = True
End Sub

But when i go and insert the code in the command button, runs it, it did nothing at all. no printing, so where is the problem?

 

[MARK858]

I can see no issue with it in a command button. Personally I would create a new Command button.
Are you sure that your command button is on the same workbook that the code is referring to?

 

[seragrefaat]

I can see no issue with it in a command button. Personally I would create a new Command button.
Are you sure that your command button is on the same workbook that the code is referring to?

I created another one and still the same problem, tomorrow i will create a hole copy of the file and see what happens, thanks alot for your patience.

 

[MARK858]

Do you have more than one file open? and if yes what is the name of the workbook with the sheets to be printed?

 

[seragrefaat]

Do you have more than one file open? and if yes what is the name of the workbook with the sheets to be printed?

No, it's only one file

 

[Michael M]

Another option....including the line BEFORE the Sub starts

Option Compare Text
Sub PrintMe()
    Dim Wks As Worksheet
    For Each Wks In ActiveWorkbook.Worksheets
        If Wks.Name <> "data" And Wks.Name <> "template" Then
           Wks.PrintOut
        End If
    Next Wks
End Sub

If that doesn't work I'd suggest you upload the file to a hosting site, like DropBox, then post the link to that file back here, so we can take a closer look at it !

 

[seragrefaat]

Another option....including the line BEFORE the Sub starts

Option Compare Text
Sub PrintMe()
    Dim Wks As Worksheet
    For Each Wks In ActiveWorkbook.Worksheets
        If Wks.Name <> "data" And Wks.Name <> "template" Then
           Wks.PrintOut
        End If
    Next Wks
End Sub

If that doesn't work I'd suggest you upload the file to a hosting site, like DropBox, then post the link to that file back here, so we can take a closer look at it !

It does work but print all sheets, and I found this code that works. Thanks for your help and patience.

Private Sub CommandButton4_Click()
Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
    If WS.Name = "Data" Or WS.Name = "Template" Then
    Else
      WS.PrintOut Preview:=False
    End If
    Next WS

End Sub