Add a new workbook and insert new sheets from VBA loop

[Eric Milne]

I have a workbook that contains a sheet formatted to hold data that is populated by VBA. The VBA runs a loop to populate the sheet, print it then clear all the populated data several times.

Instead of printing each sheet i'd like to create a new workbook, copy my blank sheet from my current workbook to the new workbook, create a separate sheet in the new workbook for each time the blank sheet is populated by the VBA loop. I then want to leave the new workbook open and unsaved so the user has to specify the name and location to save the new workbook.

I'm fairly competent in VBA usually but this is driving me nuts!

I'd really appreciate any nudges in the right direction

Eric

 

[Andrew Poulsom]

Where are you stuck? This will copy Sheet1 of the workbook containing your code to a new workbook, then create another copy in that new workbook.

Sub Test()
    Dim wbNew As Workbook
    Dim wsNew As Worksheet
    ThisWorkbook.Worksheets("Sheet1").Copy
    Set wbNew = ActiveWorkbook
    Set wsNew = ActiveSheet
'   Do stuff
    ThisWorkbook.Worksheets("Sheet1").Copy After:=wsNew
    Set wsNew = ActiveSheet
'   Do more stuff
End Sub