Macro to Save Excel as PDF using cell name - Compile Error

[pmybrc1]

Hello,

I've recorded a macro to save various files down as PDF's, but I would like to be able to make the location and filename variable, as I save these down each week in a new folder, and with a new name based on the week number.

I've looked at numerous threads on how to do this, and have followed instructions by putting formulas for my desired filenames & locations in cells within the worksheet, and using this range within VBA for saving down the document. Unfortunately, I seem to keep getting the same error, which is as below:

"Compile Error: Expected end of statement/Syntax Error"

My code for one of the sheets is below, as well as the contents of cells used for filename/location. If anyone could shed some light on what I'm doing wrong I would be very grateful!

VBA:

ChDir ".Range("Q13").Value"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
".Range("Q3").Value" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Cells:

C2: (Week No Entered) e.g. 18
Q13: ="S:\Merchandise Planning\Reports\2015\2015 PDF ARCHIVE\WEEK "&C2
Q3: =Q13&"REGION WEEK - 18.pdf"



Thanks,

 

[BlokeMan]

Try, Untested;

Sub savepdf()
Dim Fname As String

ChDir "S:\Merchandise Planning\Reports\2015\2015 PDF ARCHIVE\WEEK"
Fname = "REGION WEEK" & "-" & Range("C2").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

End Sub