Hi Folks,

I have a scenario where I print reports for management based upon job task, these reports are weekly under most cases and range across different staff levels. To counteract this and minimise code I have the following basic code

Sub PrintThisPage()

Dim ReportWeek As String
Dim ReportName As String
Dim Reportee As String

ReportWeek = Sheets("Individual Sales Rep Stats").Range("E2")
Reportee = Sheets("Individual Sales Rep Stats").Range("B3")
ReportName = "J:\General\Reporting\WE 20120902\Sales By Rep - " & Reportee & " - WE " & ReportWeek

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


End Sub
This code works but requires manual change every week for the report name and also it has too many fixed variables for my liking.

Changing the code to the include variables causes issues when using ReportWeek inside the path name, changing this to a separate variable also does not change the result, hard coding this into the code which works as above will not provide the required result due to the changes needed to be made each week.

Sub PrintThisPage()

Dim ReportWeek As String
Dim ReportName As String
Dim Reportee As String
Dim StatPath As String

StatPath = Sheets("Properties").Range("AD29")                       ' Manual path "J:\General\Reporting\"

ReportWeek = Sheets("Individual Sales Rep Stats").Range("E2")
Reportee = Sheets("Individual Sales Rep Stats").Range("B3")
ReportName = StatPath & ReportWeek & "\Sales By Rep - " & Reportee & " - WE " & ReportWeek

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub
Does anyone have any ideas on how to rectify this issue? to allow correction of my actual code base as below 

Sub PrintThisPage()
Dim ReportWeek As String
Dim ReportWeek1 As String
Dim ReportName As String
Dim ReportDay As String
Dim Reportee As String
Dim WsName As String
Dim StatPath As String
Dim Booker As String
Dim StateWeekly As String
Dim StateDaily As String
Dim SalesManager As String
Dim ThisPath As String


StatPath = Sheets("Properties").Range("AD29")                       ' old path "J:\General\Reporting\"
ReportWeek1 = Sheets("Properties").Range("AD26")
ThisPath = StatPath & "WE " & ReportWeek1

If Dir(ThisPath, vbDirectory) = "" Then
    MkDir ThisPath
End If

Select Case ActiveSheet.Name

Case "Individual Sales Rep Stats"
    SalesManager = Sheets("Individual Sales Rep Stats").Range("B1")
    ReportWeek = Sheets("Individual Sales Rep Stats").Range("E2")
    Reportee = Sheets("Individual Sales Rep Stats").Range("B3")
    ReportName = StatPath & ReportWeek1 & "\Sales By Rep - " & Reportee & " - WE " & ReportWeek & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Case "Individual Book-Confirm-Exec"
    ReportWeek = Sheets("Individual Book-Confirm-Exec").Range("E2")
    Booker = Sheets("Individual Book-Confirm-Exec").Range("B1")
    Reportee = Sheets("Individual Book-Confirm-Exec").Range("B3")

    Select Case Booker
    Case "Bookers"
        ReportName = StatPath & ReportWeek1 & "\Sales from Booker - " & Reportee & " - WE " & ReportWeek
    Case "Confirmers"
        ReportName = StatPath & ReportWeek1 & "\Sales from Confirmer - " & Reportee & " - WE " & ReportWeek
    Case "Exec"
        ReportName = StatPath & ReportWeek1 & "\Sales from Exec Sales - " & Reportee & " - WE " & ReportWeek
    End Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Case "State Sales Stats"
    StateWeekly = Sheets("State Sales Stats").Range("B1")
    ReportWeek = Sheets("State Sales Stats").Range("E2")
    Reportee = Sheets("State Sales Stats").Range("B3")
    Select Case StateWeekly
    Case "Australia"
        ReportName = StatPath & ReportWeek1 & "\Sales For - " & Reportee & " - for " & ReportWeek
    Case "Business"
        ReportName = StatPath & ReportWeek1 & "\Sales for the  " & Reportee & " - for " & ReportWeek
    Case Else
        ReportName = StatPath & ReportWeek1 & "\Sales By State - " & Reportee & " - for " & ReportWeek
    End Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Case "State Sales Stats - Daily"
    StateWeekly = Sheets("State Sales Stats - Daily").Range("B1")
    ReportDay = Sheets("State Sales Stats - Daily").Range("E1")
    Reportee = Sheets("State Sales Stats - Daily").Range("B1")
    Select Case StateWeekly
    Case "Australia"
        ReportName = StatPath & ReportWeek1 & "\Sales For - " & Reportee & " - for " & ReportDay
    Case "Business"
        ReportName = StatPath & ReportWeek1 & "\Sales for the  " & Reportee & " - for " & ReportDay
    Case Else
        ReportName = StatPath & ReportWeek1 & "\Sales By State - " & Reportee & " - for " & ReportDay
    End Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Case Else
    Dim Msg, Style, Title, Response, help, ctxt
        Msg = "You cannot print from this location" & vbNewLine & "Change Page and Print again"                 ' Define message.
        Style = vbOKOnly + vbError                                                                              ' Define buttons.
        Title = "Printing"                                                                                      ' Define title.
        Response = MsgBox(Msg, Style, Title, help, ctxt)

End Select

End Sub
Thanks in advance