Copy Sheet into New wb and Save

[jaybee3]

After a few weeks off work, I'm think I may have forgotten chunks of VBA, as I'm getting the "Save as workbook method failed" error. Any help?

So here's my current code:

Sub Exporta()

Dim wbStart As Workbook
Dim wbNew As Workbook

Set wbStart = ThisWorkbook
wsRec.Copy
Set wbNew = ActiveWorkbook
wbNew.SaveAs wbStart.Path & "\" & Format(Date, "MMM YY") & "\" & Format(Date, "DD MMM YY") & "\" & "Rec.xlsx"
wbNew.Close

End Sub

Do I need to create the folders first? I can't even remember doing that in the past?

 

[Norie]

If the folders don't exist then you would need to create them.

You would definitely done that previously too.

 

[jaybee3]

Yeah I thought that might have been the problem, so that's what I went ahead and did :P But if someone has something tidier, I'm open to suggestions.

Sub Exporta()
Dim wbStart As Workbook
Dim wbNew As Workbook
Dim sPath As String

Set wbStart = ThisWorkbook
wsRec.Copy
Set wbNew = ActiveWorkbook

If Dir(wbStart.Path & "\" & Format(Date, "MMM YY"), vbDirectory) = vbNullString Then
    MkDir wbStart.Path & "\" & Format(Date, "MMM YY")
End If

sPath = wbStart.Path & "\" & Format(Date, "MMM YY")

If Dir(sPath & "\" & Format(Date, "DD MMM YY"), vbDirectory) = vbNullString Then
    MkDir sPath & "\" & Format(Date, "DD MMM YY")
End If

sPath = sPath & "\" & Format(Date, "DD MMM YY") & "\" & "Rec.xlsx"
    
wbNew.SaveAs sPath
wbNew.Close
End Sub

 

[Norie]

Don't know it would be tidier but it you could change how you use the variables for the path/folders, perhaps even add another one just for good measure.

Something like this perhaps, it works for me saving to 'C:\May 11\03 May 11'

Check the paths/folders etc though because I'm not 100% sure I got them right.

Option Explicit

Sub Exporta()
Dim wbStart As Workbook
Dim wbNew As Workbook
Dim sPath As String
Dim strFolder As String
 
    Set wbStart = ThisWorkbook
    wsRec.Copy
    Set wbNew = ActiveWorkbook

    strFolder = wbStart.Path & "\" & Format(Date, "MMM YY")

    If Dir(strFolder, vbDirectory) = vbNullString Then
        MkDir strFolder
    End If
 
    strFolder = strFolder & "\" & Format(Date, "DD MMM YY")

    If Dir(strFolder, vbDirectory) = vbNullString Then
        MkDir strFolder
    End If
 
    sPath = strFolder & "\" & "Rec.xlsx"
 
    wbNew.SaveAs sPath
 
    wbNew.Close

End Sub