Search code examples
excelvbacopypaste

Every time I reference a newly created workbook, Excel creates another

I'm trying to do what should be very simple. Copy data from one sheet in an existing workbook, create a new workbook, paste that new data there, and then save and close the new workbook. However, every time I reference the new workbook, Excel creates another one. I must be making a very basic mistake.

    Dim oldWB, newWB As Workbook
    
    Set oldWB = ActiveWorkbook
    Set newWB = Workbooks.Add
    
    oldWB.Worksheets("Sheet1").Copy
    newWB.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    newWB.SaveAs "W:\filepath\CopyPasteTest.xlsx"
    newWB.Close
Solution

  • Worksheet.Copy without any parameters creates a new workbook.

    From the docs:

    If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied Worksheet object.

    You want to use Range.Copy:

    oldWB.Worksheets("Sheet1").UsedRange.Copy