date as filename vba

[twl2009]

Im using this line to save a worksheet as a workbook. I want to use a date from the sheet as part of the filename. Vba doesnt like it. Im pretty sure I need to use 'Format..."dd-mm-yy"' around or within 'sh.range("q3")'
But whatever ive tried is giving an error.
Any guidance gratefully received. Thanks


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}</style>ActiveWorkbook.SaveAs FileName:="/Users/User/Desktop/" & sh.Name & sh.Range("Q3")

 

[Stiuart_W]

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "Crew Database" And sh.Name <> "Labour Week" And sh.Name <> "Timesheet" Then
        sh.Copy
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="/Users/User/Desktop/" & sh.Name & " " & Format(Range("Q3"), "dd-mm-yy") _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        ActiveWorkbook.Close
     End If
Next

What your code is doing is looping through ALL Worksheets in your Workbook and
If name of Worksheet is NOT "Crew Database", "Labour Week" or "Timesheet" Then
Saving that worksheet with name "/Users/User/Desktop/" & sh.Name & " " & Format(Range("Q3"), "dd-mm-yy"
then goes to next worksheet and repeats

You should be able to verify in debug mode (I'm a bit confused as to why the ActiveWorkbook.Close line doesn't cause mayhem - probably you select don't save and the code carries on?
SO. It overwrites the pdf each time and all you will get is a pdf of the LAST Worksheet who's name is NOT "Crew Database", "Labour Week" or "Timesheet"

Does that make sense?

If you want a specific Worksheet or ALL the worksheets on your pdf you will need a different code

 

[twl2009]

Ah. That makes sense, what im trying to achieve is for all sheets, apart from the 3 mentioned to be saved as individual pdfs. Is that possible. ??

 

[Stiuart_W]

Sorry. I was talking nonsense. What your Export line does is Export as pdf the current ACTIVATED sheet. Your code was looping through the sheets but NOT activating them!

Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
    sh.Activate
    If sh.Name <> "Crew Database" And sh.Name <> "Labour Week" And sh.Name <> "Timesheet" Then
'        sh.Copy
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\Users\User\Desktop/" & sh.Name & " " & Format(Range("Q3"), "dd-mm-yy") _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
'        ActiveWorkbook.Close
     End If
Next

Also - Your "/" should be "A backslash**" ** Doesn't display the backslash!
And the sh.Copy was creating new copies of the Worksheet which was creating multiple workbooks with one worksheet in it but the sheet number going up by 1 each time

 

[twl2009]

Thanks Stiaurt. Forgive me my ignorance, but do you think there is a way of downloading the sheets as individual pdfs??

 

[Stiuart_W]

Not sure what you mean by "download"

The code I posted loops through all your sheets, saves as pdf each one (Except for those 3). Only caveat to note is you need to put the date in Q3 on EVERY sheet you want to save as pdf

 

[twl2009]

Excellent thank you, Q3 will always be the date.

 

[avicric]

Hey twl2009

sorry dint see your post... please try to add the below and it should work...sorry saw your post now...

Dim Path As String
Dim filename As String
Path = "INPUT YOUR PATH HERE"
filename = Range("Q3")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
Path & filename & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True