VBA code for renaming the file when using a macro to print and save

[Stef9910]

Hello,
If any of you awesome people could help, it would be much appreciated,
Right, I have used record macro to print a workbook and save in both .xlsm and .pdf and then return to the Input Screen, but I cannot work out how to rename the saved file according to the data in a cell,
I have 2 sheets in the workbook, one called Input Screen and one called Incident Report,

I would like to use cell A12 from the Incident Report sheet as the name.

The VBA coding I am using for the print and save is as follows,

Sub saveprint()
'
' saveprint Macro
'

'
Sheets("Incident Report").Select
ActiveWorkbook.SaveAs Filename:="C:\Users\wornhams\Desktop\NewReport.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\wornhams\Desktop\NewReport.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Input Screen").Select
End Sub

Any help would be much appreciated,

Thanks Stef

 

[Jimmypop]

Hi there and welcome to the forum...

Test the code below...

Sub saveprint()
    '
    ' saveprint Macro
    '
    '
    Dim Path        As String
    Dim filename    As String
    Path = "C:\Users\wornhams\Desktop\"
    filename = Sheets("Incident Report").Range("A12")
    Sheets("Incident Report").Select
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                          Path & filename & ".pdf", Quality:=xlQualityStandard, _
                          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                          FALSE
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
                          IgnorePrintAreas:=False
    Sheets("Input Screen").Select
End Sub

 

[Stef9910]

Hi there and welcome to the forum...

Test the code below...

Sub saveprint()
    '
    ' saveprint Macro
    '
    '
    Dim Path        As String
    Dim filename    As String
    Path = "C:\Users\wornhams\Desktop\"
    filename = Sheets("Incident Report").Range("A12")
    Sheets("Incident Report").Select
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                          Path & filename & ".pdf", Quality:=xlQualityStandard, _
                          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                          FALSE
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
                          IgnorePrintAreas:=False
    Sheets("Input Screen").Select
End Sub

Thank you for your speed reply,

Your Awesome, worked like a dream.

One very happy chappie I am

Thank you Jimmypop

 

[Jimmypop]

Thank you for your speed reply,

Your Awesome, worked like a dream.

One very happy chappie I am

Thank you Jimmypop

Glad we could assist and thanks for the positive feedback would you maybe mark the post / code I gave as a solution. it would assist other as well should they have the same problem... Thanks

 

[Stef9910]

Glad we could assist and thanks for the positive feedback would you maybe mark the post / code I gave as a solution. it would assist other as well should they have the same problem... Thanks

Hi Jimmypop,
Just a quick question,
How would I change the path so it is not specific to my user name, so any user could use it?
Thank you for your patience
Stef

 

[Jimmypop]

Hi Jimmypop,
Just a quick question,
How would I change the path so it is not specific to my user name, so any user could use it?
Thank you for your patience
Stef

Hi

Maybe try...

Sub saveprint()
    '
    ' saveprint Macro
    '
    '
    Dim Path        As String
    Dim filename    As String
    Path = Environ$("Userprofile") & "\Desktop"
    filename = Sheets("Incident Report").Range("A12")
    Sheets("Incident Report").Select
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                          Path & filename & ".pdf", Quality:=xlQualityStandard, _
                          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                          False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
                          IgnorePrintAreas:=False
    Sheets("Input Screen").Select
End Sub

 

[Stef9910]

Thank you very much Jimmypop,
Work great

 

[Jimmypop]

Pleasure