Copying 2 worksheets to a new workbook, and saving 2

[t16turbo]

Hi there,

I have a macro that generates all sorts of wonderful things.
I need to copy 2 worksheets "ConsolidatedFOB" and "Capacity and Headcount" into a new workbook.

These need to be saved without any macros - (kind of like a copy and paste special into the new book).

Can anyone suggest a clean and easy way of achieving this?

 

[WalterContrata]

Hello t16turbo,

I would try recording the act of copying, pasting, and saving with the macro recorder. The resulting code could be modified to fit your application, maybe like the following.

Option Explicit

Sub SaveWorkSheets()
    Dim oOldWorkbook As Workbook
    Set oOldWorkbook = ActiveWorkbook
    
    Dim oNewWorkBook As Workbook
    Set oNewWorkBook = Workbooks.Add
    
    Dim oOldSh As Worksheet
    Set oOldSh = oOldWorkbook.Worksheets("ConsolidatedFOB")
    oOldSh.Cells.Copy
    
    Dim oNewSh As Worksheet
    Set oNewSh = oNewWorkBook.Worksheets("Sheet1")
    oNewSh.Name = "ConsolidatedFOB"
    oNewSh.Range("A1").Activate
    oNewSh.Paste
    
    oNewWorkBook.SaveAs Filename:="ConsolidatedFOB"

End Sub

You might need the PasteSpecial method instead of Paste, and you might prefer not to copy all of the cells in the sheet... but is this the kind of thing that you are looking for?

Best Regards,
Walter

 

[WalterContrata]

t16turbo,

Sorry, I forgot clean-up again.

...
    Set oOldSh = Nothing
    Set oOldWorkbook = Nothing
    
    Set oNewSh = Nothing
    Set oNewWorkBook = Nothing

End Sub

Best Regards,
Walter