vba SaveAs code doesn't work from excel macro enabled template

[MakeExcelTalk]

Hello, I have a bit of vba code that saves a copy worksheet from my workbook as a .prn (text file). See code below.
This code works well from the macro enabled workbook, but it does not work from a macro enabled TEMPLATE. Any ideas?

Code;
' Save sheet as .PRN file
Sheet5.Select
Sheet5.Copy
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\" & "Import File PRN " & Format(Date, "ddmmyy") & ".prn", FileFormat:=xlTextPrinter _
, CreateBackup:=False
ActiveWindow.Close savechanges:=False

 

[Reggie74]

It worked on my pc .. ( WIN 7 , MS Excel 2010)
Macro enabled template (*.xltm)
File saved successfully as (*.prn)

' Save sheet as .PRN file
Sub test()


    Sheet5.Select
    Sheet5.Copy
    ActiveWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & "\" & "Import File PRN " & Format(Date, "ddmmyy") & ".prn", FileFormat:=xlTextPrinter _
    , CreateBackup:=False
    ActiveWindow.Close savechanges:=False


End Sub

 

[MakeExcelTalk]

Thanks Reggie,
I still have the problem. I am also running MS Excel 2010.
Tried creating a new workbook and copied the codes across. Worked fine when it was a macro enabled workbook, but when I saved as macro enabled template it comes up with the error "Run-time error '1004'. Method 'SaveAs' of object '_workbook' failed" with the options to End or Debug
At this point the worksheet has been renamed per the vba code, but not saved as a .prn file

 

[Reggie74]

Try this ... Note that you don't need to copy sheet 5 . When you select sheet 5 and save as a prn then only the active sheet can be saved as a prn so no need to copy it.

' Save sheet as .PRN file
Sub test()


    Sheet5.Select
'    Sheet5.Copy
    ActiveWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & "\" & "Import File PRN " & Format(Date, "ddmmyy") & ".prn", FileFormat:=xlTextPrinter _
    , CreateBackup:=False
    
    ActiveWindow.Close savechanges:=False




End Sub

Thanks Reggie,
I still have the problem. I am also running MS Excel 2010.
Tried creating a new workbook and copied the codes across. Worked fine when it was a macro enabled workbook, but when I saved as macro enabled template it comes up with the error "Run-time error '1004'. Method 'SaveAs' of object '_workbook' failed" with the options to End or Debug
At this point the worksheet has been renamed per the vba code, but not saved as a .prn file

 

[MakeExcelTalk]

Try this ... Note that you don't need to copy sheet 5 . When you select sheet 5 and save as a prn then only the active sheet can be saved as a prn so no need to copy it.

Still not working Sorry to waste your time. I appreciate your input.
I have removed the code that copies the sheet 5 as you suggested and it comes up with exactly the same error.
To check that it wasn't just my installation of Excel, I have tried it from another PC with Excel 2010, and it comes up with the same error (also only on the template, not the macro enabled workbook).
Anyone got any ideas?

 

[MakeExcelTalk]

Try this ... Note that you don't need to copy sheet 5 . When you select sheet 5 and save as a prn then only the active sheet can be saved as a prn so no need to copy it.

Sorry to waste your time. Your input is appreciated.
The error is still coming up even though I have removed the code to copy sheet 5 like you suggest.
To check that it isn't only my installation of Excel at fault, I tried this code on another PC with Excel 2010, and it came up with the same error (template only, error doesn't come up on the macro enabled workbook)
Anyone got any ideas?