thelostscott
Board Regular
- Joined
- May 7, 2010
- Messages
- 226
Hi all,
Need some help if you are willing to share your gifts....
I have a Workbook that has multiple sheets in it that I need to then seperate into individual Workbooks saving the files as the same name as what is on the current tab. I would be looking at doing this for around 40-odd sheets.
What I have so far is this:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1:J72").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-33
Range("L8").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
""S:\****\****\****\****\****\****\15.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("*******************.xls").Activate
Range("M73").Select
Sheets(Array(15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58)).Select
ActiveWindow.SmallScroll Down:=-60
Range("A1:J72").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("F7").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"S:\****\****\****\****\****\****\15.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
* So I take it I would have to put in a "ActiveWorkbook.SaveAs Filename:=Activesheet.name" somewhere?
* This is also done just by recording a Macro, however I adjusted the Array to select the individual sheets
Need some help if you are willing to share your gifts....
I have a Workbook that has multiple sheets in it that I need to then seperate into individual Workbooks saving the files as the same name as what is on the current tab. I would be looking at doing this for around 40-odd sheets.
What I have so far is this:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1:J72").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-33
Range("L8").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
""S:\****\****\****\****\****\****\15.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("*******************.xls").Activate
Range("M73").Select
Sheets(Array(15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58)).Select
ActiveWindow.SmallScroll Down:=-60
Range("A1:J72").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("F7").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"S:\****\****\****\****\****\****\15.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
* So I take it I would have to put in a "ActiveWorkbook.SaveAs Filename:=Activesheet.name" somewhere?
* This is also done just by recording a Macro, however I adjusted the Array to select the individual sheets