Macro to print sheets to .pdf and save to a folder

[Will85]

Each cell in sheet "Publish" column c contains a sheet name
Each cell in sheet "Publish" column j contains half of the file path and name to be saved to .pdf

So C1 might say "Balance Sheet" (which is an actual tab in my workbook)
and J1 might say "2022\Financials\ABC Company\2022 05 - Balance Sheet

My macro right now is:

Sheets("Publish").Select
Sheets(CStr(Range("C1"))).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"https://something.something.com/sites/Accounting/" & Worksheets("Publish").Range("J1").Value & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

I currently repeat the above code for each sheet that I need to publish. So Ill repeat the above and C1 becomes C2 and J1 becomes J2, so on and so on. I have 30 or so sheets that Ill create, so I have to repeat this about 30 times.

Instead of repeating the above code over and over, Id like something more elegant, so that I dont have to call out the individual rows in the macro, but rather just loop through each cell in C and J and create a .pdf for each.

I am trying to do something like this, but I know this doesnt make sense.

Dim ary As Variant
Dim sht As Variant

With Sheets("Publish")
ary = .Range("J1", .Range("J" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"https://company.sharepoint.com/sites/Accounting/" & Worksheets("Publish").Range("J1").Value & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End With
Next sht

 

[HighAndWilder]

This works fine for me.

I like to break the code down as it is easier to maintain in the future.

You will need to change the path (strPath variable).

Public Sub subExportToPDF()
Dim rngExport As Range
Dim rng As Range
Dim WsPublish As Worksheet
Dim WsExport As Worksheet
Dim strPath As String
Dim strFileName As String
Dim strMsg As String

    ActiveWorkbook.Save

    Set WsPublish = Worksheets("Publish")

    Set rngExport = WsPublish.Range("J1", WsPublish.Range("J" & Rows.Count).End(xlUp))
    
    strPath = ActiveWorkbook.Path
    
    For Each rng In rngExport.Cells
     
        Set WsExport = Worksheets(WsPublish.Cells(rng.Row, 3).Value)
    
        strFileName = strPath & "\" & rng.Value & ".pdf"
                
        WsExport.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
   
        strMsg = strMsg & vbCrLf & strFileName
   
    Next rng

    MsgBox strMsg, vbInformation, "PDF files created"

End Sub

 

[mohadin]

Hi
See if this what you need

Sub test()

    Dim ary As Variant
    Dim ary1 As Variant
    Dim sht As Variant
    Dim c&
    With Sheets("Publish")
        ary1 = .Range("C1", .Range("C" & Rows.Count).End(xlUp))
        ary = .Range("J1", .Range("J" & Rows.Count).End(xlUp)).Value
    End With
    For Each sht In ary1
        With Sheets(sht)
            .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                             "https://company.sharepoint.com/sites/Accounting/" & ary(c, 1) & ".pdf" _
                                           , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                   :=False, OpenAfterPublish:=False
            c = c + 1
        End With
    Next sht
End Sub

 

[Will85]

Hi
See if this what you need

Sub test()

    Dim ary As Variant
    Dim ary1 As Variant
    Dim sht As Variant
    Dim c&
    With Sheets("Publish")
        ary1 = .Range("C1", .Range("C" & Rows.Count).End(xlUp))
        ary = .Range("J1", .Range("J" & Rows.Count).End(xlUp)).Value
    End With
    For Each sht In ary1
        With Sheets(sht)
            .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                             "https://company.sharepoint.com/sites/Accounting/" & ary(c, 1) & ".pdf" _
                                           , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                   :=False, OpenAfterPublish:=False
            c = c + 1
        End With
    Next sht
End Sub

How can I change . ActiveSheet.ExportAsFixedFormat

To a specific sheet name? I am running the macro from a different tab. Tab name is "Control"

 

[Will85]

How can I change . ActiveSheet.ExportAsFixedFormat

To a specific sheet name? I am running the macro from a different tab. Tab name is "Control"

Or maybe thats not the problem. I am getting a "subscript out of range error"

 

[HighAndWilder]

Are you saying that the sheet names and file names in columns C and J respectively are not in the 'Publish' worksheet anymore but in a worksheet
names 'Control'?

 

[Will85]

Are you saying that the sheet names and file names in columns C and J respectively are not in the 'Publish' worksheet anymore but in a worksheet
names 'Control'?

No, Im getting a subscript out of range error, I thought it was because I was running the macro from a button on a different sheet, but I dont think thats the error.

The sheet names and file names are in columns C and J on the Publish worksheet.

But I wasnt using your code, I was using the second response. Ill try your code now.

 

[Will85]

What did you mean by this?

"You will need to change the path (strPath variable)."

 

[HighAndWilder]

The following line:

strPath = ActiveWorkbook.Path

needs to change to

strPath = "https://company.sharepoint.com/sites/Accounting/"

if that is where you want the PDF files saved.

 

[Will85]

It appears to be working. How do I get rid of the list of published sheets at the end, but simply keep the msg that it was published?