Runtime 1004 Error with code that seemed to be working... Help!

[dropkickweasel]

Hi,

I'm new to macros and don't understand why code that seemed to be working stopped after I'd saved the file and opened it up again an hour or so later without having made any changes to it...

I'm trying to get a .pdf file of a specific print area run from a macro and for the file to be saved as a value from a cell (E4) and the date (format "dd-mm") with a space between them if possible. This is the code I had copied and modified as best I could:

Sub SavePDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:/Test/" & Range("E4").Value & "_" & Format(Date, "dd-mm") _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

It seemed to work fine, creating a .pdf in a folder named "Test", but has since stopped working. If it makes a difference the value in cell E4 will always be a four digit number.

If anyone could tell me what the correct syntax is for it to always print from the third sheet in my workbook (which is titled "SWANS", rather than the active sheet, I would appreciate that too.

Also, how could I replace the "_" which separated the cell value and the date with a " "? When I tried like that, it replaced it with "%20".

Any help appreciated, and many thanks in advance.

 

[BrianB]

1. FWIW I copied and pasted your code and it worked correctly. Are you sure the folder c:\test exists ? Are you sure that the file does not exist already ?
2. Instead of ActiveSheet. put Worksheets(3). or Worksheets("SWANS").
3. We cannot save filenames containing "*" or "?" because the file system uses these characters for a special search purpose (* = any set of characters, ? = any single character).

 

[dropkickweasel]

Thanks BrianB.

Not sure why it stopped working, but I re-recorded the save to pdf macro actions and it's now working fine and saving to the folder I want to save to.

Thanks for the tip with changing it from activesheet to worksheet(3), that's really helpful.

Many thanks for taking the time to reply and help me out :]