Saving selected sheets as pdf

[Bagsy]

Hi everybody
Been away for a while but lucky enough to just get a new job, hoping someone can help me out a bit on the last part of a VBA project. Using Office 2010

I have been working on some code to save selected sheets in an excel workbook as a named PDF document. The name is a cell value (D5) on a sheet (PSW) which is the only sheet in the workbook I don’t need as pdf. The file path as you see is hardcoded.
The Error is this line ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & filename
The code seems to be running OK but can’t be sure as the file is not saving in the correct location (or may not be saving at all as I can’t find it)
Thanks in advance, much appreciated
Gary

Sub PDF()
Dim Ws As Worksheet
Dim CurrentSheet As Worksheet
Dim wb As Workbook
Dim filename As String
Dim x As Long
Application.ScreenUpdating = False

ChDrive "W:\"
    filename = Worksheets("PSW").Range("D5").Text & ".pdf"
Path = "W:\QA\QA Main\Inspection Reports\_To Be Approved"


For x = 2 To ThisWorkbook.Sheets.Count
    If Sheets(x).name <> "PSW" Then Sheets(x).Select Replace:=False
Next x
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & filename

Sheets("PSW").Select
Application.ScreenUpdating = True
    
End Sub

 

[Bagsy]

Ok I have now a few more details, the code is working apart from as below

1. Its saving it to the root folder where I have my work in progress sheet (not the coded file path)
2. Its naming the file “Path & Filename” (it’s not picking up the variable name in cell D5 or the hard coded file path
3. Its converting the complete workbook to pdf not the selected sheets
Thanks in advance

 

[Bagsy]

Thanks to anybody who has looked at my code but I have it running now, bit of trial and error
The problem with the code converting my top sheet PSW, was because I was running the code from that sheet so it was selected by default. I got round this by the VBA selecting another sheet first then reselecting the sheet PSW at the end.
The rest of it was just luck and a break.
For anybody who is interested my cobbled together code is below. Please don’t judge I’m sure that somebody could do a better job. But it works for me

Sub PDF()
Dim Ws As Worksheet
Dim CurrentSheet As Worksheet
Dim wb As Workbook
Dim filename As String
Dim Path As String
Dim x As Long
Dim mySaveFile As Variant

Application.ScreenUpdating = False
Sheets("Del COC").Select
ChDrive "W:\"
    ChDir "W:\QA\QA Main\Inspection Reports\_To Be Approved"
mySaveFile = Worksheets("PSW").Range("D5").Text & ".pdf"

For x = 2 To ThisWorkbook.Sheets.Count
    If Sheets(x).name <> "PSW" Then Sheets(x).Select Replace:=False
Next x
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=mySaveFile

Sheets("PSW").Select
Application.ScreenUpdating = True
    
End Sub