Excel 2010 VBA - How to Copy the current workbook (with VBA modules)

I have a workbook BUDGET.XLSM and it has code that I need and want.  I want to
1) make a copy of this workbook
2) name the new workbook "NEW BUDGET.XLSM"  
3) in the new copy. I want to delete an unnecessary worksheet named "OLD".

The code that will be executing this will be resident in budget.xlsm.

Can someone help me with this code?
brothertruffle880Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ButlerTechnologyConnect With a Mentor Commented:
The following code will do what you are looking for...
Public Sub DuplicateWorkbook()

ActiveWorkbook.SaveCopyAs "New Budget.xlsm"
Workbooks.Open "New Budget.xlsm"
ActiveWorkbook.Sheets("OLD").Delete
End Sub

Open in new window


Tom
0
 
Martin LissOlder than dirtCommented:
Just do File|SaveAs from Excel.
0
 
David L. HansenProgrammer AnalystCommented:
Just in case you were thinking "copy WORKSHEET to a different WORKBOOK" here's the code:
Sub CopyWorksheets()

Dim wbGenerated as Workbook, wbTemplate as Workbook
Dim ws as Worksheet

Set wbGenerated = Workbooks("Generated.xlsx")
Set wbTemplate = Workbooks("TemplateASA.xlsx") 'If this line fails, remove it and uncomment the following line
' Set wbTemplate = Workbooks.Open("TemplateASA.xlsx", ,True) ' Opens as read only
For Each sh In wbTemplate.Worksheets
   sh.Copy After:=wbGenerated.Sheets(wbGenerated.Sheets.Count)
Next sh
'wbTemplate.Close(False) 'Only required if you opened wbTemplate above

End sub

Open in new window

Taken from here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.