sheet not saving in correct filepath, instead its saving it to My Documents

[dmcgov]

I have the following code, working except that its not saving the new spreadsheet in the correct folder. (instead it is saving it to My Documents folder). I need it to save in the parent folder of the initial spreadsheet.
I added a line (now commented out) that does save the file in the correct path, but then the macro does a dump and fails further down the line.

Sub CreatePO()
    'copy sheet to new workbook
    Dim wb As Workbook
    
    abc = ActiveSheet.Name
    Sheets("formulas").Range("AB1") = Sheets(abc).Range("I39")
    
    Set wb = Workbooks.Add
    
    ThisWorkbook.ActiveSheet.Copy Before:=wb.Sheets(1)

    'save workbook as xlsm
    wb.SaveAs ActiveSheet.Name, FileFormat:=52
    'wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52

    Dim openworkbook
    Dim x1
    
    Dim FNametxt As String, FNamefrm As String, FileSelected As String, FileName As String
        With ThisWorkbook
        Let FNametxt = .Path & "\PO\code.txt"
        .VBProject.VBComponents("Module1").Export FNametxt
        Let FNamefrm = .Path & "\PO\form.frm"
        .VBProject.VBComponents("UserForm1").Export FNamefrm
        Let FNamecal = .Path & "\PO\calendar.frm"
        .VBProject.VBComponents("frmCalendar").Export FNamecal
        End With

    Workbooks.Open abc

    Sheets(abc).Activate
    ActiveWorkbook.VBProject.VBComponents.Import FNametxt
    ActiveWorkbook.VBProject.VBComponents.Import FNamefrm
    ActiveWorkbook.VBProject.VBComponents.Import FNamecal
    ActiveWorkbook.Save
    
    Set openworkbook = Workbooks.Open(abc)
     
    Application.ScreenUpdating = False
    
     With ThisWorkbook
        .Sheets("Formulas").Copy Before:=openworkbook.Sheets(1)
        .Sheets("Master").Copy Before:=openworkbook.Sheets(1)
        .Sheets("BigMaster").Copy Before:=openworkbook.Sheets(1)
        .Sheets("Estimating1").Copy Before:=openworkbook.Sheets(1)
        .Activate
    End With
    
    'close purchase order-template, DONT SAVE
    Workbooks("Purchase Order-template.xlsm").Activate
        'Application.DisplayAlerts = False
        'ActiveWorkbook.Close
        'Application.DisplayAlerts = True
        'another way to close the file without seeing any prompts
    Workbooks("Purchase Order-template.xlsm").Close SaveChanges:=False
  End Sub

when i uncomment out this line, the macro fails

    'wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52

can anyone help me with this?

[Kenneth Hobson]

I don't see anything wrong with that. I seldom rely on relative file references. I use full drive:\path\filename.ext paths.

[dmcgov]

when i put the full path in, the macro fails here:

     With ThisWorkbook
        .Sheets("Formulas").Copy Before:=openworkbook.Sheets(1)
        .Sheets("Master").Copy Before:=openworkbook.Sheets(1)
        .Sheets("BigMaster").Copy Before:=openworkbook.Sheets(1)
        .Sheets("Estimating1").Copy Before:=openworkbook.Sheets(1)
        .Activate
    End With

thoughts?

[Norie]

If you don't specify the path to save file it will be saved in what Excel/VBA sees as the current directory, which may or may not be what you want/think it is.

How does the code fail when you use this?

wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52

Also, why are you saving the newly added workbook here,

    wb.SaveAs ActiveSheet.Name, FileFormat:=52

then, even though you haven't closed it, re-opening it here,

 Workbooks.Open abc

and then again re-opening it.

 Set openworkbook = Workbooks.Open(abc)

Actually, could you explain what the code is actually meant to do?

Is it meant to save a specific worksheet from the workbook the code is in to a new workbook and add code from the original workbook to the new workbook?

[dmcgov]

yes this statement is correct: Is it meant to save a specific worksheet from the workbook the code is in to a new workbook and add code from the original workbook to the new workbook?

i have a workbook that contains multiple PO tabs. i need to save them as i use them. so when i put this in my code

wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52

and that code works, it saves it into the parent directory of the source file.

but when i do that it fails here:

     With ThisWorkbook
        .Sheets("Formulas").Copy Before:=openworkbook.Sheets(1)
        .Sheets("Master").Copy Before:=openworkbook.Sheets(1)
        .Sheets("BigMaster").Copy Before:=openworkbook.Sheets(1)
        .Sheets("Estimating1").Copy Before:=openworkbook.Sheets(1)
        .Activate
    End With

[Norie]

What exactly do you want/not want in the new 'PO' workbook(s)?

[dmcgov]

so i made a change to the code, it's failing on Workbooks("Purchase order-Template").Sheets("Formulas").Copy Before:=openworkbook.Sheets(1), gives me a run time error '91', object variable or with block variable not set

Sub CreatePO()
    'copy sheet to new workbook
    Dim wb As Workbook
    
    abc = ActiveSheet.Name
    Sheets("formulas").Range("AB1") = Sheets(abc).Range("I39")
    
    Set wb = Workbooks.Add
    
    ThisWorkbook.ActiveSheet.Copy Before:=wb.Sheets(1)
    
    'save workbook as xlsm
    'wb.SaveAs ActiveSheet.Name, FileFormat:=52
    wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52

    Dim openworkbook
    Dim x1
    
    Dim FNametxt As String, FNamefrm As String, FileSelected As String, FileName As String
        With ThisWorkbook
        Let FNametxt = .Path & "\PO\code.txt"
        .VBProject.VBComponents("Module1").Export FNametxt
        Let FNamefrm = .Path & "\PO\form.frm"
        .VBProject.VBComponents("UserForm1").Export FNamefrm
        Let FNamecal = .Path & "\PO\calendar.frm"
        .VBProject.VBComponents("frmCalendar").Export FNamecal
        End With

    Workbooks.Open abc

    Sheets(abc).Activate
    ActiveWorkbook.VBProject.VBComponents.Import FNametxt
    ActiveWorkbook.VBProject.VBComponents.Import FNamefrm
    ActiveWorkbook.VBProject.VBComponents.Import FNamecal
    ActiveWorkbook.Save
    
    Set openworkbook = Workbooks.Open(abc)
     
    Application.ScreenUpdating = False
    
     'With Workbooks("Purchase order-Template")
        Workbooks("Purchase order-Template").Activate
        Workbooks("Purchase order-Template").Sheets("Formulas").Copy Before:=openworkbook.Sheets(1)
        Workbooks("Purchase order-Template").Sheets("Master").Copy Before:=openworkbook.Sheets(1)
        Workbooks("Purchase order-Template").Sheets("BigMaster").Copy Before:=openworkbook.Sheets(1)
        Workbooks("Purchase order-Template").Sheets("Estimating1").Copy Before:=openworkbook.Sheets(1)
        Workbooks("Purchase order-Template").Activate
    'End With
    
    'close purchase order-template, DONT SAVE
    Workbooks("Purchase Order-template.xlsm").Activate
        'Application.DisplayAlerts = False
        'ActiveWorkbook.Close
        'Application.DisplayAlerts = True
        'another way to close the file without seeing any prompts
    Workbooks("Purchase Order-template.xlsm").Close SaveChanges:=False

End Sub

thoughts?

[dmcgov]

so the new workbook should contain just one PO, add code to it, add supporting sheets for the macro to run.

the issue is where to put the "With ThisWorkbook" statements so that they work.

[TMS]

Probably missing something here, but the variable abc, as far as I can see contains a sheet name. Where do you add a file extension to the file name of the workbook?

[dmcgov]

Probably missing something here, but the variable abc, as far as I can see contains a sheet name. Where do you add a file extension to the file name of the workbook?

the format is found on this line:

wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52

which saves it as a .xlsm format

[dmcgov]

so i fixed it. made the following changes and now it is working as expected.

     With Workbooks("Purchase order-Template.xlsm")
        .Activate
        .Sheets("Formulas").Copy Before:=Workbooks(abc).Sheets(1)
        .Sheets("Master").Copy Before:=Workbooks(abc).Sheets(1)
        .Sheets("BigMaster").Copy Before:=Workbooks(abc).Sheets(1)
        .Sheets("Estimating1").Copy Before:=Workbooks(abc).Sheets(1)
        'Workbooks("Purchase order-Template.xlsm").Activate
    End With

[TMS]

Might save it with that format, but does it add the extension for you? I've always created the full file path, file name and file extension. Something I must try

Thanks for the rep.