I have a command button that when pressed, saves a active sheet and an accompanying sheet from same workbook as a PDF. The code works great if I enter the non-active sheets name in the Sheet Array. However, I need the second (non-active sheet) to be variable, dependent on what is in a specific cell. Here is the code that works without the variable non-active sheet.
Sub Save_Clear()
'
' Save_Clear Macro
' Will save Form as PDF to AE file and Rest dropdowns to blank and clear content on row 3.
Dim strFilename As String
Dim rngRange As Range
'Considering Sheet1 to be where you need to pick file name
Set rngRange = Worksheets("Approval Form").Range("H5")
'Create File name with dateStamp
strFilename = rngRange.Value
Sheets(Array("Approval Form", "VariableName")).Select
Sheets("Approval Form").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\xyz\Desktop\Working\New\" & strFilename & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Sheets("VariableName").Select
Sheets("Approval Form").Select
Sheets("Approval Form").Unprotect
Range("C8").Select
Selection.ClearContents
Range("D9").Select
Selection.ClearContents
Range("D10").Select
Selection.ClearContents
Range("A3:F3").Select
Selection.ClearContents
Sheets("Approval Form").Protect
Sheets("VariableName").Select
End Sub
Bookmarks