VBA to save all indicated in range tabs as all separated PDFs with name of the tabs in the same place where the excel file is

[Jorgi]

Dear All,

I hope that you will be able to help. I have a macro that works perfectly but I need to change/modify it so it will be possible to save all indicated in range tabs as all separated PDFs with name of the tabs as name of the PDFs in the same folder where is the excel file with macro.

VBA to save all the indicated tabs in range A2:A5 as 4 separated PDFs with file name as the tab name e.g., EXCEL TAB 1AAA - save as 1AAA.pdf in the same folder where the main macro file is. In this case macro will create 4 PDFs at once in the main file location. 1AAA.pdf, 3CCC.pdf, 4DDD.pdf, 6FFF.pdf Thank you for your help.

Below macro that save all the TABS from Range A2:A5 as one PDF file not 4 separated files like I want to.

Option Explicit

Sub Save_All_tabs_As_PDF()

Dim PDFfile As String
Dim currentSheet As Worksheet
Dim replaceFlag As Boolean
Dim cell As Range

PDFfile = ThisWorkbook.Path & "\1AAA.pdf"

Set currentSheet = ActiveSheet
With ActiveSheet
replaceFlag = True
For Each cell In .Range("A2:A5")
If cell.Value <> vbNullString Then
Worksheets(cell.Value).Select replaceFlag
replaceFlag = False
End If
Next
If Not replaceFlag Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
currentSheet.Select
MsgBox "PDF created"
Else
MsgBox "No Tabs to save at the moment"
End If
End With

End Sub

 

[Anfinsen]

Hello, You have to specify the file name. see the modified code below, let me know if this works for you.

Option Explicit

Sub Save_All_tabs_As_PDF()

Dim PDFfile As String
Dim currentSheet As Worksheet
Dim replaceFlag As Boolean
Dim cell As Range
' added
Dim Fname as String

PDFfile = ThisWorkbook.Path & "\1AAA.pdf"

Set currentSheet = ActiveSheet
With ActiveSheet
replaceFlag = True
For Each cell In .Range("A2:A5")
If cell.Value <> vbNullString Then
Worksheets(cell.Value).Select replaceFlag
replaceFlag = False
End If
Next
If Not replaceFlag Then
'added
Fname = cell.value
'added ref to fname as Filename
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
currentSheet.Select
MsgBox "PDF created"
Else
MsgBox "No Tabs to save at the moment"
End If
End With

End Sub

 

[Jorgi]

Hello, You have to specify the file name. see the modified code below, let me know if this works for you.

Option Explicit

Sub Save_All_tabs_As_PDF()

Dim PDFfile As String
Dim currentSheet As Worksheet
Dim replaceFlag As Boolean
Dim cell As Range
' added
Dim Fname as String

PDFfile = ThisWorkbook.Path & "\1AAA.pdf"

Set currentSheet = ActiveSheet
With ActiveSheet
replaceFlag = True
For Each cell In .Range("A2:A5")
If cell.Value <> vbNullString Then
Worksheets(cell.Value).Select replaceFlag
replaceFlag = False
End If
Next
If Not replaceFlag Then
'added
Fname = cell.value
'added ref to fname as Filename
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
currentSheet.Select
MsgBox "PDF created"
Else
MsgBox "No Tabs to save at the moment"
End If
End With

End Sub

​

Hello, Anfinsen the code I added is when I normally save all sheets in a range as only one PDF. I would like to have a code that I will be able to save all sheets in a range as individual PDFs. All the saved PDFs will have their sheets names as the PDF file title e.g., 1AAA.pdf 3CCC.pdf, 4DDD.pdf, 6FFF.pdf not one PDF containing all the sheets from the range. Thank you and I apologise as I probably wasn't clear enough.

 

[Anfinsen]

Okay, just so I understand, when someone inputs a matching named tab value in Range(A2:A5), you want to print the corresponding named tab, to an individual PDF?

 

[Jorgi]

Okay, just so I understand, when someone inputs a matching named tab value in Range(A2:A5), you want to print the corresponding named tab, to an individual PDF?

Yes, that's correct save all the tabs from Range(A2:A5)/content from each tab in separated/individual PDF files and all the PDF files will be saved in the same place/folder where the main excel workbook is. Thank you so much for your patience and help

 

[Jorgi]

Dear All Excel Gurus I hope this is not too complicated. I wasn't able to figure the correct way hope you will be able to help. I would like to explain again what I'm looking for to avoid any confusion. In a range (A2:A10) on sheet "MAIN" will be stored sheets' names that need to be save as PDFs. Every single sheet will need to be save as individual separated file in the same folder as the excel file.

Example below and the macro outcome will be 4 separated PDF files (1AAA.pdf 3CCC.pdf, 4DDD.pdf, 6FFF.pdf) save in the same location like the excel file. In addition (If possible) to create individual folders for every PDF will be excellent. The folders will need the same sheets/PDF name so folder 1AAA with 1AAA.pdf file and next folder called 3CCC with 3CCC.pdf file inside etc., Many Thanks