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]

That is good.

Whose answer are you using?

Do you want to delete the worksheets?

 

[Dedeke]

To Will85

Maybe this code can help you out.
I use a button called "save to Pdf"
Pushing this button let's me select a range wich i want as pdf.
After selecting the desired range it gives me the possibility to save the file where i want.

Sub KlPil_NatAsPDF()

Dim SelectedRange As Range
Dim saveLocation As String
On Error Resume Next

' to selet the range

Set SelectedRange = Application.InputBox(prompt:= _
"Selecteer een bereik", Title:="Maak een PDF file", Type:=8)

On Error GoTo 0

If SelectedRange Is Nothing Then
MsgBox "Geen bereik geselecteerd" & vbNewLine & "Of U heeft geannuleerd", vbCritical, "Maak een PDF file"


Else

saveLocation = Application.GetSaveAsFilename(FileFilter:= _
         "PDF Files (*.pdf), *.pdf", Title:="PDF opslaan", _
        InitialFileName:=("KlasPilNat.pdf"))


'Save a range as PDF
SelectedRange.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

End If
End Sub