Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy worksheets and save files dynamically
Hello
I've been trying to write a macro to copy each worksheet within a workbook and then save each worksheet as it's own file. However I would like to do this dynamically (ideally to keep the macro short and easier to follow) so that the worksheet is selected based on a cell reference and the filename it is saved as is also based on a cell reference. I've searched the forum and tried to use some of the solutions, to similar queries, from there - which led me to try and use called subroutines. This seemed like a neater soloutin but I keep getting various error messages; and I'm not familiar enough with macros to work out what the problems are. I've copied the "long" macro that I've developed so far and tried to comment on what I would like to do (I hope that isn't patronising). Sub SB001000() ' Application.DisplayAlerts = False ' This is the workbook containing the worksheets to be copied and saved Workbooks.Open Filename:= _ "H:\Fin Management\Education, etc\Education 2009-10\School Reports\Budget Reports\Current month\School Budget Reports 08-02-10.xls" ' Each worksheet has a similar name (e.g. Output (001000); Output (001001) etc) Ideally dynamically obtain these names based on a range of cell reference - e.g. cell A1 contains the name Output (001000); cell A2 = Output (001001) etc Sheets("Output 1 (001000)").Select Sheets("Output 1 (001000)").Copy ' Each new workbook would be saved with a similar name (e.g. 001001 School Budget) ActiveWorkbook.Saveas Filename:= _ "H:\Fin Management\Education, etc\Education 2009-10\School Reports\Budget Reports\Current month\001000 School Budget.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Workbooks("001000 School Budget.xls").Close ' Once all worksheets have been copied and saved as a new workbook then close the "master" workbook Workbooks("School Budget Reports 08-02-10.xls").Close Application.DisplayAlerts = True End Sub Thanks for any help |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save worksheets as seperate files (I know how to do it one by one) | Excel Discussion (Misc queries) | |||
Save copy of folder with linked files | Excel Worksheet Functions | |||
dynamically enter name of last modified by in spreadsheet on save | Excel Discussion (Misc queries) | |||
Save worksheets to different files | Excel Worksheet Functions | |||
Excel 2000 worksheets save incorrectly as notepad files. | Excel Discussion (Misc queries) |