Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save several worksheets into one file
I have a master file with 25 worksheets. I need to to prerpare four new excel
file from the master sheet. The first file should contain few sheets from master sheets, second should contain few, third & so on. For example: First file should contain Sheet1, Sheet3, Sheet7, Sheet10 First file should contain Sheet11, Sheet13, Sheet17, Sheet22 and so on. The names in real file are different, could this be done. This is something very IMP for me, please help. Thanks a lot for help in advance. Boss |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save several worksheets into one file
These two macros should help
the 1st macro put the sheet names into the active sheet in the workbook. This is to get the 25 sheet names. Then add into column b (manually) the workbook names (including path) where you want the worksheets to go. The second macro reads column A and B of the active worksheet and places the worksheets into the correct workbooks. Sub sheetnames() RowCount = 1 For Each sht In Sheets Range("A" & RowCount) = sht.Name RowCount = RowCount + 1 Next sht End Sub Sub copysheets() Set oldbk = ThisWorkbook Set oldsht = oldbk.ActiveSheet RowCount = 1 Do While oldsht.Range("A" & RowCount) < "" Workbooks.Open Filename:=oldsht.Range("B" & RowCount) Set newbk = ActiveWorkbook Sheets(oldsht.Range("A" & RowCount)).Copy _ after:=newbk.Sheets(newbk.Sheets.Count) newbk.Close RowCount = RowCount + 1 Loop End Sub "Boss" wrote: I have a master file with 25 worksheets. I need to to prerpare four new excel file from the master sheet. The first file should contain few sheets from master sheets, second should contain few, third & so on. For example: First file should contain Sheet1, Sheet3, Sheet7, Sheet10 First file should contain Sheet11, Sheet13, Sheet17, Sheet22 and so on. The names in real file are different, could this be done. This is something very IMP for me, please help. Thanks a lot for help in advance. Boss |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save several worksheets into one file
Thanks a lot for ur help..
The first macro is working fine but the second is giving me runtime error 13 "Type mismatch" I kept the entire path of the file in second col. Macro is able to get into the file also. Please help & let me know where i am worng. thanks a lot Boss "Joel" wrote: These two macros should help the 1st macro put the sheet names into the active sheet in the workbook. This is to get the 25 sheet names. Then add into column b (manually) the workbook names (including path) where you want the worksheets to go. The second macro reads column A and B of the active worksheet and places the worksheets into the correct workbooks. Sub sheetnames() RowCount = 1 For Each sht In Sheets Range("A" & RowCount) = sht.Name RowCount = RowCount + 1 Next sht End Sub Sub copysheets() Set oldbk = ThisWorkbook Set oldsht = oldbk.ActiveSheet RowCount = 1 Do While oldsht.Range("A" & RowCount) < "" Workbooks.Open Filename:=oldsht.Range("B" & RowCount) Set newbk = ActiveWorkbook Sheets(oldsht.Range("A" & RowCount)).Copy _ after:=newbk.Sheets(newbk.Sheets.Count) newbk.Close RowCount = RowCount + 1 Loop End Sub "Boss" wrote: I have a master file with 25 worksheets. I need to to prerpare four new excel file from the master sheet. The first file should contain few sheets from master sheets, second should contain few, third & so on. For example: First file should contain Sheet1, Sheet3, Sheet7, Sheet10 First file should contain Sheet11, Sheet13, Sheet17, Sheet22 and so on. The names in real file are different, could this be done. This is something very IMP for me, please help. Thanks a lot for help in advance. Boss |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save several worksheets into one file
I made some minor changes
Sub copysheets() Set oldbk = ThisWorkbook Set oldsht = oldbk.ActiveSheet RowCount = 1 Do While oldsht.Range("A" & RowCount) < "" Workbooks.Open Filename:=oldsht.Range("B" & RowCount) Set newbk = ActiveWorkbook oldbk.Sheets(oldsht.Range("A" & RowCount).Value).Copy _ after:=newbk.Sheets(newbk.Sheets.Count) newbk.Close SaveChanges:=True RowCount = RowCount + 1 Loop End Sub "Boss" wrote: Thanks a lot for ur help.. The first macro is working fine but the second is giving me runtime error 13 "Type mismatch" I kept the entire path of the file in second col. Macro is able to get into the file also. Please help & let me know where i am worng. thanks a lot Boss "Joel" wrote: These two macros should help the 1st macro put the sheet names into the active sheet in the workbook. This is to get the 25 sheet names. Then add into column b (manually) the workbook names (including path) where you want the worksheets to go. The second macro reads column A and B of the active worksheet and places the worksheets into the correct workbooks. Sub sheetnames() RowCount = 1 For Each sht In Sheets Range("A" & RowCount) = sht.Name RowCount = RowCount + 1 Next sht End Sub Sub copysheets() Set oldbk = ThisWorkbook Set oldsht = oldbk.ActiveSheet RowCount = 1 Do While oldsht.Range("A" & RowCount) < "" Workbooks.Open Filename:=oldsht.Range("B" & RowCount) Set newbk = ActiveWorkbook Sheets(oldsht.Range("A" & RowCount)).Copy _ after:=newbk.Sheets(newbk.Sheets.Count) newbk.Close RowCount = RowCount + 1 Loop End Sub "Boss" wrote: I have a master file with 25 worksheets. I need to to prerpare four new excel file from the master sheet. The first file should contain few sheets from master sheets, second should contain few, third & so on. For example: First file should contain Sheet1, Sheet3, Sheet7, Sheet10 First file should contain Sheet11, Sheet13, Sheet17, Sheet22 and so on. The names in real file are different, could this be done. This is something very IMP for me, please help. Thanks a lot for help in advance. Boss |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save several worksheets into one file
It worked..
Thanks a lot for ur help.. your help made my job easier.. Thanks a lot "Boss" wrote: I have a master file with 25 worksheets. I need to to prerpare four new excel file from the master sheet. The first file should contain few sheets from master sheets, second should contain few, third & so on. For example: First file should contain Sheet1, Sheet3, Sheet7, Sheet10 First file should contain Sheet11, Sheet13, Sheet17, Sheet22 and so on. The names in real file are different, could this be done. This is something very IMP for me, please help. Thanks a lot for help in advance. Boss |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell2003 (SP-1) File > Save and File > Save As.. grayed out | Excel Discussion (Misc queries) | |||
save button in excel to save one of the worksheets with a cell value as its name | Excel Programming | |||
Excel marcos firing on file save as but not file save | Excel Programming | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming | |||
Save As - Multiple Sheets fails to save as text file | Excel Programming |