Save new workbook in same location as source workbook code

[Samson92]

I have a workbook that has multiple sheets, and I have a macro that will copy and paste three select sheets into a new workbook. I'd like to add an automatic save on the new workbook, but i'd like it to save in two places. One being the same place as the workbook that runs the macro, the other being in G:\MI\. If possible, i'd like the name of the new workbook to be "BR - today's date". Any help would be appreciated. Here's the code I am working off now.

Sub CopyInNewWB()    Dim wbO As Workbook, wbN As Workbook


    On Error GoTo ErrHandler


    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False


    Set wbO = ActiveWorkbook
    Set wbN = Workbooks.Add


    wbO.Sheets("Tracking").Copy wbN.Sheets(1)
    With ActiveSheet.UsedRange
       .Value = .Value
    End With


    wbO.Sheets("Bridge").Copy wbN.Sheets(2)
    With ActiveSheet.UsedRange
       .Value = .Value
    End With


    wbO.Sheets("Overview (Age)").Copy wbN.Sheets(3)
    With ActiveSheet.UsedRange
       .Value = .Value
    End With


    wbN.Sheets("Sheet1").Delete
    wbN.Sheets("Bridge").Activate


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True


ErrHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
       
 
        
End Sub

Thanks.

 

[mumps]

Try:

Sub CopyInNewWB()
    Dim wbO As Workbook, wbN As Workbook, ws As Worksheet
    On Error GoTo ErrHandler
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    Set wbO = ThisWorkbook
    Sheets(Array("Tracking", "Bridge", "Overview (Age)")).Copy
    For Each ws In Sheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Sheets("Bridge").Activate
    With ActiveWorkbook
        .SaveAs Filename:=wbO.Path & Application.PathSeparator & "MI\BR-" & Date & ".xlsx", FileFormat:=51
        .SaveAs Filename:="G:\MI\BR-" & Date & ".xlsx", FileFormat:=51
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
ErrHandler:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

 

[Samson92]

Thanks for that suggestion, but it doesn't save anything, still just opens the new workbook with the name Book2 and doesn't save. Is there anything else I need to add?

 

[mumps]

Try:

Sub CopyInNewWB()
    Dim wbO As Workbook, wbN As Workbook, ws As Worksheet
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    Set wbO = ThisWorkbook
    Sheets(Array("Tracking", "Bridge", "Overview (Age)")).Copy
    For Each ws In Sheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Sheets("Bridge").Activate
    With ActiveWorkbook
        .SaveAs Filename:=wbO.Path & Application.PathSeparator & "BR-" & Replace(Date, "/", "-") & ".xlsx", FileFormat:=51
        .SaveAs Filename:="G:\MI\BR-" & Date & ".xlsx", FileFormat:=51
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

 

[Samson92]

That one gives runtime error 1004. Thank you for trying to help with though, I appreciate it.

 

[mumps]

I tested the macro on a dummy workbook and it worked properly. Which line of code is highlighted when you click "Debug"? I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Samson92]

I changed the location of the source workbook and it works now. Thank you for your help.

 

[mumps]

You are very welcome.