How to export each worksheet from a excel file to PDF

[ThrottleWorks]

Hi,

I have one excel file.
This file may have n number of worksheets.
For example, 50. Out of these there will be 4 pre-defined worksheets.


I need to exclude these 4 worksheets and export remaining 46 worksheets as PDF files.
So I will have 46 different PDF files saved from one file.

Let us say, file has 50 worksheets, 4 worksheets are Yamaha, Suzuki, Honda, KTM.
Apart from Yamaha, Suzuki, Honda, KTM, I need to export each worksheet as PDF and save.

Also, 4 worksheet parameter is different for each file.
One file might have 3 worksheets to exclude, another might have 4 to exclude.

I will have that list ready and stored in macro workbook.
Can anyone please help me in this.

 

[Kenneth Hobson]

Sub Main()
  Dim p$, ws As Worksheet, a, fn$
  Dim fso As Object
  'Dim fso As FileSystemObject
 
  p = ThisWorkbook.Path & "\"
  a = ThisWorkbook.Worksheets(1).Range("A2", _
    ThisWorkbook.Worksheets(1).Cells(Rows.Count, "A").End(xlUp))
 
  Set fso = CreateObject("Scripting.FileSystemObject")
  For Each ws In ActiveWorkbook.Worksheets
    If PosInArray(ws.Name, a) <> -1 Then
      fn = p & ws.Name & ".pdf"
      ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn _
      , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
      :=False, OpenAfterPublish:=False
    End If
  Next ws
 
  Set fso = Nothing
  MsgBox "All done..."
End Sub

'If array is 0 based, 1 is returned if aValue matches anArray(0).
Function PosInArray(aValue, anArray)
  Dim pos As Long
  On Error Resume Next
  pos = -1
  pos = WorksheetFunction.Match(CStr(aValue), anArray, 0)
  PosInArray = pos
End Function

 

[ThrottleWorks]

Hi @Kenneth Hobson sir, thanks a lot for the help.
I am checking this and will revert with details.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @Kenneth Hobson sir, thanks a lot for the help.
It is working great. Have a nice day ahead.