Auto save as pdf by having file name as sheet name in Excel VBA Macro

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
204
Office Version
  1. 2019
Platform
  1. Windows
I have 27 sheets in an Excel file and file name is blank.xlsm. Except the sheet name COUNT and RAW DATA, I want that all other sheets to be save as PDF in the address
C:\Users\jyotirmaya\Desktop\Macro by using file name as Sheet name, all other 25 sheets have unique names. I have data from Column A to H and the sheets need to save as pdf from 1st row to till the last row where data is available.

kindly help with the code
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Untested here. May need some adjusting :

VBA Code:
'This method Saves each of the worksheets of current
'Excel file as individual PDF File using Excel VBA
Sub SaveEachSheetAsPDFFile()
                            
Dim wsA     As Worksheet
Dim wbA     As Workbook
Dim WS_Count  As Long
Dim strFile, myfile As String

Set wbA = ActiveWorkbook
WS_Count = wbA.Worksheets.Count

For Each wsA In wbA.Sheets

        wsA.Activate
        
        'create default name for savng file
        strFile = "C:\Users\jyotirmaya\Desktop\Macro" & wsA.Name & ".pdf"
        myfile = strFile

        'export to PDF if a folder was selected
        If myfile <> "False" Then
            If wsA.Name <> "Count" Or wsA.Name <> "Raw Data" Then
             ActiveSheet.ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=myfile, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
            End If
            
        End If

     Next
End Sub
 
Upvote 0
Solution
'This method Saves each of the worksheets of current
'Excel file as individual PDF File using Excel VBA
Sub SaveEachSheetAsPDFFile()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim WS_Count As Long
Dim strFile, myfile As String

Set wbA = ActiveWorkbook
WS_Count = wbA.Worksheets.Count

For Each wsA In wbA.Sheets

wsA.Activate

'create default name for savng file
strFile = "C:\Users\jyotirmaya\Desktop\Macro\" & wsA.Name & ".pdf"
myfile = strFile

'export to PDF if a folder was selected
If myfile <> "False" Then
If wsA.Name <> "Count" Or wsA.Name <> "Raw Data" Then
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myfile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If

End If

Next
End Sub

Thanks its working, but it was also saving the two sheets RAW DATA and COUNT. I dont want to save these two sheets. & what to add in the code if I want to create a file name as the sheet name + todays date. ?

Again thanks
 
Upvote 0
See if this works :

VBA Code:
If wsA.Name = "Count" Or "Raw Data" Then
     'no code here
else
             ActiveSheet.ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=myfile, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
End If

Then edit the strFile code line to :

strFile = "C:\Users\jyotirmaya\Desktop\Macro\" & wsA.Name & " " & Format(Date, "mm-dd-yy") & ".pdf"
 
Upvote 0
VBA Code:
If wsA.Name = "Count" Or "Raw Data" Then
'no code here
else
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myfile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If

I have sheet name "Count" and "Raw Data" still when I used the above code I am getting error - Run time Error 13, Type Mismatch.

final code look like this

VBA Code:
'This method Saves each of the worksheets of current
'Excel file as individual PDF File using Excel VBA
Sub SaveEachSheetAsPDFFileED()
                            
Dim wsA     As Worksheet
Dim wbA     As Workbook
Dim WS_Count  As Long
Dim strFile, myfile As String

Set wbA = ActiveWorkbook
WS_Count = wbA.Worksheets.Count

For Each wsA In wbA.Sheets

        wsA.Activate
        
        'create default name for savng file
        strFile = "C:\Users\jyotirmaya\Desktop\Macro\" & wsA.Name & " " & Format(Date, "mm-dd-yy") & ".pdf"
        myfile = strFile

        'export to PDF if a folder was selected
       If wsA.Name = "Count" Or "Raw Data" Then
     'no code here
Else
             ActiveSheet.ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=myfile, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
End If


     Next
End Sub
 
Upvote 0
Change this line :

VBA Code:
If wsA.Name = "Count" Or "Raw Data" Then

To this :

Code:
If wsA.Name = "Count" Or wsA.Name = "Raw Data" Then
 
Upvote 0
Thanks now working, My COUNT and RAW DATA sheet was in UPPERCASE, Thanks for your valuable time and help. :):):):):)
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top