• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

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?
0
brothertruffle880
Asked:
brothertruffle880
1 Solution
 
Martin LissRetired ProgrammerCommented:
Just do File|SaveAs from Excel.
0
 
ButlerTechnologyCommented:
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now