copy a project to a new workbook

**calmena** · 03-25-2011, 12:44 PM

What I need is when a user clicks a button, a new workbook is opened and is exactly the same as the source workbook so everything gets copied including userforms, etc.

This is what I have so far:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/21/2010 by Stewart Neuman
'
' Keyboard Shortcut: Ctrl+n
'
    Dim srcWB As Workbook
    Dim destWb As Workbook
 
    Application.ScreenUpdating = False    ' Prevents screen refreshing.

    Set srcWB = ActiveWorkbook
    Set destWb = Workbooks.Add

    srcWB.Activate      'activates source workbook
    srcWB.VBProject.Copy        'copies source project including all sheets, formulas, userforms, and modules, etc.
    destWb.Paste        'pastes new project in new workbook
    srcWB.Close     'closes source workbook
    destWb.Activate     'activates new workbook

unfortunately this line doesn't work of course

srcWB.VBProject.Copy        'copies source project including all sheets, formulas, userforms, and modules, etc.

but what I have done is written in the comments what I would like to happen,but i'm not sure on how to do it exactly. can anyone help?

thanks in advance!

**royUK** · 03-25-2011, 12:48 PM

Why not just create a template workbook?

**calmena** · 03-25-2011, 01:02 PM

Because I have other code to follow that I just didn't post in this forum that won't allow me to do that.

**royUK** · 03-25-2011, 01:19 PM

I don't understand. A template workbook should contain all the code, userforms, formulas, formatting etc ready to input data to

**calmena** · 03-25-2011, 01:46 PM

Let me explain. There are three parts to my workbook. part one and two are on the same worksheet. part one is header information the user enters once per week, part two is content the user enters once per day.

this is how it works. day one, the user opens the blank workbook, enters all header info. and content for the day, save the file and closes it for the day. day two, the user opens day one's file, hit the macro button, and a new workbook is generated maintaining all of day one's part one header info, but clear out part two contents, then saves the file as a new name for day two. this process continues.

part three is in each workbook contains an array of hidden formulas for use behind the scenes.

maybe a template would work, but i've never used one and would have to learn the VBA code so that the button opens the template and to me it would perform the same task.

**royUK** · 03-25-2011, 03:15 PM

Can you attach a workbook example.

**calmena** · 03-25-2011, 04:23 PM

Sure. Thanks for you help!

**calmena** · 03-25-2011, 05:06 PM

In module1, there is code at the bottom that is commented out. this code used to be placed at the top in place of the code I'm working with now, to copy the workbook to a new workbook, however, it doesn't copy the userforms so I couldn't use it.