Save PDF as Activesheet name cell value

[KyleG]

Hi,

Can someone help me change this recorded macro to save the pdf file name. Instead of test I want the file saved as the Worksheet Name and the value of B2.
So it might be DeptA 201917, or DeptB 201918 as examples.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Data\test.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

I want this to all happen from another worksheet called Entry and all the Dept pages are actually hidden. With this formula I would need to unhide them and make them the activesheet, but could each sheet be saved without activating them?

Cheers for your help. I haven't done any VBA for about 5 years and I ant find anything in my old notes to assist.

 

[Trebor76]

Hi Kyle,

What's the logic for the code to know how to PDF which tabs? Is it all hidden tabs, do the relevant tab names have "dept" in them, something else?

Robert

 

[KyleG]

So we have DeptA, DeptB, and DeptC as the hidden department sheet names, the code would apply to all of them. It does a number of other things to other pages and the final step I want it to do is take the 3 Dept worksheets and export these as PDFs as DeptA 201917, DeptB 201917, and DeptC 201917, next week cell B2 on each of these worksheets will have updated to 201918 (along with other data) and so when the macro is run it will save a new file DeptA 201918, deptB 201918 and DeptC 201918.

is that what I needed to explain better?

 

[Trebor76]

Yes, that's great. Try this:

Option Explicit
Sub Macro2()

    Dim wsMySheet As Worksheet
    Dim varMySheet As Variant
    
    Application.ScreenUpdating = False
    
    For Each varMySheet In Array("DeptA", "DeptB", "DeptC") 'Sheets to have PDF's created from. Change to suit.
        Set wsMySheet = Sheets(CStr(varMySheet))
        wsMySheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Data\" & wsMySheet.Range("B2").Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Next varMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert