how to... copy a workbook and rename using a range of cells (name define-there are 2 of them but only one listed below for testing). Each filename should be a concatenate string. And should close the file after creating. 1Sub createbudgetforms()
2Dim SaveDriveDir As String, MyPath As String
3Dim FName As Variant, N As Long
4Dim rnum As Long, destrange As Range
5Dim sh As Worksheet
6ChDir "c:\Budget\2011"
7Workbooks.Open(Filename:="c:\Budget\2011\IT_BudgetTemplate_.xls"). _
8RunAutoMacros Which:=xlAutoOpen
9Windows("Operatingbudgettemplate.xls").Activate
10Sheets("data").Select
11Application.Goto Reference:="Budget_forms"
12Range("B4:B60").Select
13ActiveWorkbook.SaveAs Filename:=FName, _
14FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
15ReadOnlyRecommended:=False, CreateBackup:=False
16GetData FName(N), "budget_forms", "b4:b60", destrange.Offset(1, 0), False, False
17ActiveWorkbook.SaveAs Filename:=file_name
18ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
19ActiveWorkbook.Close
20Next
21End If
22End Sub
|
Similar Threads
