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")

 

[twl2009]

I have tried the 'OpenAfterPublish:=True', it has the same result.
I also tried getting rid of 'Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas ... etc'
Same result again.
Its odd because the pdf saves fine, but there is a new excel workbook of the sheet being saved, with a new name 'workbook ..' and a number that goes up every time I test the code, currently on 21.

 

[Stiuart_W]

Post ALL your code


Please

 

[twl2009]

Ok, that is all the code in the save macro. Would you like to see the workbook code?

 

[Stiuart_W]

Within reason, yes! (Ie if it's many pages ....... we may not want to see it. But something odd is clearly happening

 

[Martvg]

Did you also delete the "ActiveWorkbook.Close" line when you tried "Openafterpublish:=True"?

 

[twl2009]

I figured out the problem, but dont know the code to solve it. Basically there is a sheet that is copied each time a new name is entered and when the pdf is saved it also copies the sheets, I think the original copying is still in the clipboard and this maybe causing the new workbook to be created.
I will post all of it in a sec.

 

[twl2009]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>



Private Sub Worksheet_Change(ByVal Target As Range)




Application.ScreenUpdating = False




Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim isect1 As Range
Dim isect2 As Range
Dim isect3 As Range
Dim isect4 As Range






If Target.Count > 1 Then Exit Sub




If Target.Address(False, False) = "B2" Then Call TitleCheck




If Target.Address(False, False) = "E2" Then Call CompanyCheck




If Target.Address(False, False) = "G2" Then Call DateCheck








Set Rng1 = Range("A11:G22")
Set Rng2 = Range("A26:G34")
Set Rng3 = Range("A38:G46")
Set Rng4 = Range("A50:G58")
Set isect1 = Intersect(Target, Rng1)
Set isect2 = Intersect(Target, Rng2)
Set isect3 = Intersect(Target, Rng3)
Set isect4 = Intersect(Target, Rng4)


If isect1 Is Nothing And isect2 Is Nothing And isect3 Is Nothing And isect4 Is Nothing Then Exit Sub




If Application.CountIf(ActiveSheet.UsedRange, Target.Value) = 1 Then
    Set MyActiveCell = ActiveCell
    Sheets("Timesheet").Cells.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Name = Target.Value
End If




Sheets("Labour Week").Activate
MyActiveCell.Select


Application.ScreenUpdating = True




[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR]/CODE]

 

[twl2009]

And the Macros

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>



Sub TitleCheck()
 
    Application.ScreenUpdating = False


    With Sheets("Labour Week")
        Productiontitle = .Range("B2")
            
            If IsEmpty(Productiontitle) Then
                    Application.Goto Worksheets("Labour Week").Range("B2")
                Else
                    ActiveSheet.Unprotect
                        ActiveSheet.Range("E2").Locked = False
                             Application.Goto Worksheets("Labour Week").Range("E2")
                        ActiveSheet.Range("B2:C2").Locked = True
                    ActiveSheet.Protect
            End If
    End With


    Application.ScreenUpdating = True


End Sub




Sub CompanyCheck()


    Application.ScreenUpdating = False


    With Sheets("Labour Week")
        ProductionCompany = .Range("E2")
            
            If IsEmpty(ProductionCompany) Then
                    Application.Goto Worksheets("Labour Week").Range("E2")
                Else
                    ActiveSheet.Unprotect
                        ActiveSheet.Range("G2").Locked = False
                             Application.Goto Worksheets("Labour Week").Range("G2")
                        ActiveSheet.Range("E2").Locked = True
                    ActiveSheet.Protect
            End If
    End With


    Application.ScreenUpdating = True


End Sub




Sub DateCheck()
  
    Application.ScreenUpdating = False
    
    With Sheets("Labour Week")
        Weekending = .Range("G2")
            
            If Not IsDate(Weekending) Then
                Application.Goto Worksheets("Labour Week").Range("G2")
            Else
                Call NewLabourSheet
            End If


    End With


    Application.ScreenUpdating = True


End Sub




Sub NewLabourSheet()


    Application.ScreenUpdating = False


    ActiveSheet.Unprotect
    ActiveSheet.Range("A5:G70").Locked = False
    ActiveSheet.Range("A2:G2").Locked = True
    ActiveSheet.Range("D1").Locked = False
    ActiveSheet.Protect


    Application.Goto Worksheets("Labour Week").Range("A5")


    ActiveWorkbook.SaveAs FileName:="/Users/User/Desktop/" & "Labour Week " & Range("B2").Value & " " & Format(Range("G2"), "mm-dd-yy")


    Application.Goto Worksheets("Labour Week").Range("A5")


    Application.ScreenUpdating = True


End Sub


Sub saveSheets()


    Application.ScreenUpdating = False


    Dim sh As Worksheet
    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


    Application.ScreenUpdating = True


[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR]/CODE]

 

[Stiuart_W]

In you NewLabourSheet Sub you have a Save Workbook command which may be doing what you are seeing. Also

Sub saveSheets()


    Application.ScreenUpdating = False


    Dim sh As Worksheet
    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


    Application.ScreenUpdating = True


[COLOR=#011993][FONT=Menlo]End [/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR]

sh.copy and ActiveWorkbook.Close seem superfluous (Unless you really want the Workbook closed after creating your pdf

 

[twl2009]

So ive just realised that the saveSheets macro is actually saving the wrong sheet as a pdf. It has the correct sheetname, but is actually a pdf of the sheet that is copied when a new name is inputed into the spreadsheet. This is really confusing me, especially as it worked fine when it was just saving as an excel workbook.