A VBA code to print all sheets except sheet1and sheet2

seragrefaat

Board Regular
Joined
Nov 16, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Nov 16, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Nov 16, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Nov 16, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,049
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Another option....including the line BEFORE the Sub starts
VBA Code:
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

Board Regular
Joined
Nov 16, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Another option....including the line BEFORE the Sub starts
VBA Code:
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top