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

[Jyotirmaya]

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

 

[Logit]

Untested here. May need some adjusting :

'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

 

[Jyotirmaya]

'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

 

[Logit]

See if this works :

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"

 

[Jyotirmaya]

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

'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

 

[Logit]

Change this line :

If wsA.Name = "Count" Or "Raw Data" Then

To this :

If wsA.Name = "Count" Or wsA.Name = "Raw Data" Then

 

[Jyotirmaya]

If wsA.Name = "Count" Or wsA.Name = "Raw Data" Then

Still these two sheets are saved as pdf, ?

 

[Logit]

That is not the case here. My test workbook does what you are seeking.

???

Here is a link to download my example. You'll need to change the path to match your computer.
PDF Sheets Save Except.xlsm

 

[Jyotirmaya]

Thanks now working, My COUNT and RAW DATA sheet was in UPPERCASE, Thanks for your valuable time and help.

 

[Logit]

You are welcome. Cheers !