Add a new workbook and insert new sheets from VBA loop

Eric Milne

New Member
Joined
Apr 27, 2005
Messages
36
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! :devilish:

I'd really appreciate any nudges in the right direction :biggrin:

Eric
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.

Code:
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
 

Forum statistics

Threads
1,137,063
Messages
5,679,401
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top