VB Code to generate PDF of multiple sheets

A

ateebali

Board Regular
Joined
Dec 13, 2018
Messages
108
Dear Sir
I am using following code in sheet: Summary
I also one more sheet "Report" which I wanted to save in same pdf
both sheets in same workbook, need to amend this vb code please


Code: 
Sub PDFActiveSheet()
Dim PDFFileName As String
'
       
     PDFFileName = GeneratedFileName(ThisWorkbook.Path & "\TCS # " & Range("O6").Value, 0)




    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True




End Sub




Function GeneratedFileName(fileName As String, i As Integer)
Dim testFileName As String
    testFileName = fileName
    If i <> 0 Then
        testFileName = fileName & " (" & i & ")"
    End If
    GeneratedFileName = testFileName
    If Dir(testFileName & ".pdf", vbNormal) > "" Then
        GeneratedFileName = GeneratedFileName(fileName, i + 1)
    End If
    
End Function
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
If the Summary sheet and Report sheet are next to each other in the workbook, in that order, and you want them saved in the PDF in the same order then:

Code: 
    With ActiveWorkbook
        .Worksheets("Summary").Select
        .Worksheets("Report").Select False
    End With
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
Otherwise if none of those criteria are fulfilled you would have to copy the 2 sheets to a temporary workbook, in the correct order and export (save) the temporary workbook as a PDF file. For example code which would need modifying for your situation see https://www.mrexcel.com/forum/excel...particular-order-post5192370.html#post5192370
 
Upvote 0
Should I do this but its not working

Sub PDFActiveSheet()


Dim PDFFileName As String
'

PDFFileName = GeneratedFileName(ThisWorkbook.Path & "\TCS # " & Range("O6").Value, 0)


With ActiveWorkbook
.Worksheets("Summary").Select
.Worksheets("Report").Select False
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True




End Function




Function GeneratedFileName(fileName As String, i As Integer)
Dim testFileName As String
testFileName = fileName
If i <> 0 Then
testFileName = fileName & " (" & i & ")"
End If
GeneratedFileName = testFileName
If Dir(testFileName & ".pdf", vbNormal) > "" Then
GeneratedFileName = GeneratedFileName(fileName, i + 1)
End If

End Function
 
Upvote 0
Sir, following code working fine, but I also wanted to add following code in this which I am dding but its not working, need help


Working Fine
Sub PDFActiveSheet()
Dim PDFFileName As String
'
For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh
Sheets(Array("Report", "Summary")).Select
Sheets("Summary").Activate

PDFFileName = GeneratedFileName(ThisWorkbook.Path & "\TCS # " & Range("O6").Value, 0)


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True




End Sub




Function GeneratedFileName(fileName As String, i As Integer)
Dim testFileName As String
testFileName = fileName
If i <> 0 Then
testFileName = fileName & " (" & i & ")"
End If
GeneratedFileName = testFileName
If Dir(testFileName & ".pdf", vbNormal) > "" Then
GeneratedFileName = GeneratedFileName(fileName, i + 1)
End If


End Function

Need to add
 Sheets(Array("Consolidated Report", "Welcome", "New Style", "Garment Detail", _
"Picture", "Operations", "Machines Data", "Layout", "Report")).Select
Sheets("Report").Activate
ActiveWindow.SelectedSheets.Visible = False
Range("F3").Select
Sheets("Short").Select
ActiveWindow.SelectedSheets.Visible = False
 
Upvote 0
ateebali said:
Need to add
 Sheets(Array("Consolidated Report", "Welcome", "New Style", "Garment Detail", _
"Picture", "Operations", "Machines Data", "Layout", "Report")).Select
Sheets("Report").Activate
ActiveWindow.SelectedSheets.Visible = False
Range("F3").Select
Sheets("Short").Select
ActiveWindow.SelectedSheets.Visible = False
Click to expand...
Have you tried adding those sheets to the first group:

Code: 
Sheets(Array("Report", "Summary", "Consolidated Report", "Welcome", "New Style", "Garment Detail", _
        "Picture", "Operations", "Machines Data", "Layout", "Report")).Select

PDFFileName = GeneratedFileName(ThisWorkbook.Path & "\TCS # " & Range("O6").Value, 0)


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
As I said in my first reply you can only group multiple sheets like this and save them in the same order in the created PDF if the sheets are in the same order in the workbook as they are in the Array list. Otherwise you have to export them to a temporary workbook in the required order.

Your GeneratedFileName function looks incorrect because it tests for fileName.pdf, but doesn't return the .pdf part to the caller.
 
Upvote 0
Dear Sir
Your code is not working, yes my sheets are in sequence which I wanted to export as PDF, like
Sheet: "Report" and "Summary" are next to each other from left to right, I want to export both as PDF
the export button is in sheet "Summary" and "Report" sheet is hidden with a vb code, so I first unhiding all sheets with this code;

For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh

Then I am selecting both sheets to export as pdf;

Sheets(Array("Report", "Summary")).Select
Sheets("Summary").Activate

Now I am giving name to PDF and also giving condition to not replace thr file and instead save new file with extension
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True




End Sub




Function GeneratedFileName(fileName As String, i As Integer)
Dim testFileName As String
testFileName = fileName
If i <> 0 Then
testFileName = fileName & " (" & i & ")"
End If
GeneratedFileName = testFileName
If Dir(testFileName & ".pdf", vbNormal) > "" Then
GeneratedFileName = GeneratedFileName(fileName, i + 1)
End If

This is perfectly fine and working well, now I wanted to hide all other sheets except "Summary"
Sheets(Array("Consolidated Report", "Welcome", "New Style", "Garment Detail", _
"Picture", "Operations", "Machines Data", "Layout", "Report")).Select
Sheets("Report").Activate
ActiveWindow.SelectedSheets.Visible = False
Range("F3").Select
Sheets("Short").Select
ActiveWindow.SelectedSheets.Visible = False

This is not working
 
Upvote 0
I misunderstood what you are trying to do. To hide the sheets try this:
Code: 
    Dim sheetName As Variant
    
    For Each sheetName In Array("Consolidated Report", "Welcome", "New Style", "Garment Detail", _
            "Picture", "Operations", "Machines Data", "Layout", "Report")
        Worksheets(sheetName).Visible = xlSheetHidden  'or xlSheetVeryHidden
    Next
xlSheetHidden allows the user to unhide a sheet via the Excel UI. xlSheetVeryHidden means only VBA can unhide it.
 
Upvote 0
Dear Sir, Requesting you to write e complete code like below;
Sub PDFActiveSheet()
Dim PDFFileName As String
'
For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh
Sheets(Array("Report", "Summary")).Select
Sheets("Summary").Activate

PDFFileName = GeneratedFileName(ThisWorkbook.Path & "\TCS # " & Range("O6").Value, 0)


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFFileName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True




End Sub




Function GeneratedFileName(fileName As String, i As Integer)
Dim testFileName As String
testFileName = fileName
If i <> 0 Then
testFileName = fileName & " (" & i & ")"
End If
GeneratedFileName = testFileName
If Dir(testFileName & ".pdf", vbNormal) > "" Then
GeneratedFileName = GeneratedFileName(fileName, i + 1)
End If
Dim sheetName As Variant

For Each sheetName In Array("Consolidated Report", "Welcome", "New Style", "Garment Detail", _
"Picture", "Operations", "Machines Data", "Layout", "Report")
Worksheets(sheetName).Visible = xlSheetHidden 'or xlSheetVeryHidden
Next
End Function
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Question Question
saves in xlsx format instead of PDF
Replies
6
Views
254
KlausW
KlausW
S
  • Solved
Sheets selection based on tab color
Replies
2
Views
85
SoerenB
S
N
  • Question Question
Emailing from Excel and automatically attaching pdf
Replies
1
Views
366
dermie_72
D
M
  • Question Question
Excel VBA Save PDF within workbook folder and send as email attachment
Replies
1
Views
1K
rpaulson
rpaulson
KlausW
  • Solved
Save location based on Cell value
Replies
3
Views
235
SunnyAlv
SunnyAlv

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

Share this page

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