Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-11-18
3
Medium Priority
?
673 Views
Last Modified: 2013-11-18
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
Comment
Question by:brothertruffle880
3 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39657953
Just do File|SaveAs from Excel.
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 2000 total points
ID: 39658046
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39658049
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question