|
copy sheets
-- bobbiI have a workbook with 30 worksheets labeled Abook1 to Abook10, Bbook1 to Bbook10, Cbook1 to Cbook10. The worksheet name is repeated in the same cell on each sheet. I would like to save each group of 10 into seperate workbooks named A, B, and C (using only the first digit of each sheet name). Then make sure each workbook has the sheets in numerical order (they won't necessarily be completed in order). Is there a macro that will do all that automatically? |
copy sheets
What do you mean "Using only the first digit of each sheet", that would give you problems as you cannot have more than one sheet named the same! bobbi;454183 Wrote: -- bobbiI have a workbook with 30 worksheets labeled Abook1 to Abook10, Bbook1 to Bbook10, Cbook1 to Cbook10. The worksheet name is repeated in the same cell on each sheet. I would like to save each group of 10 into seperate workbooks named A, B, and C (using only the first digit of each sheet name). Then make sure each workbook has the sheets in numerical order (they won't necessarily be completed in order). Is there a macro that will do all that automatically? -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125723 |
copy sheets
Thank you for your response.
I suppose I wasn't very clear. I want to direct each group into a separate workbook for each group (A, B and C). The full worksheet name can be used. -- bobbi "Simon Lloyd" wrote: What do you mean "Using only the first digit of each sheet", that would give you problems as you cannot have more than one sheet named the same! bobbi;454183 Wrote: -- bobbiI have a workbook with 30 worksheets labeled Abook1 to Abook10, Bbook1 to Bbook10, Cbook1 to Cbook10. The worksheet name is repeated in the same cell on each sheet. I would like to save each group of 10 into seperate workbooks named A, B, and C (using only the first digit of each sheet name). Then make sure each workbook has the sheets in numerical order (they won't necessarily be completed in order). Is there a macro that will do all that automatically? -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125723 |
copy sheets
This should do what you need: Code: -------------------- Sub move_n_group_sheets() Dim Arr As Varriant, a As Long Arr = Array("A", "B", "C") For a = 0 To 2 Sheets(Array(Arr(a) & "Book1", Arr(a) & "Book2", Arr(a) & "Book3", _ Arr(a) & "Book4", Arr(a) & "Book5", Arr(a) & "Book6", Arr(a) & _ "Book7", Arr(a) & "Book8", Arr(a) & "Book9", Arr(a) & "Book10")).Move ActiveWorkbook.SaveAs (Arr(a) & ".xls") ActiveWorkbook.Close False Next a End Sub -------------------- bobbi;454210 Wrote: Thank you for your response. I suppose I wasn't very clear. I want to direct each group into a separate workbook for each group (A, B and C). The full worksheet name can be used. -- bobbi "Simon Lloyd" wrote: What do you mean "Using only the first digit of each sheet", that would give you problems as you cannot have more than one sheet named the same! bobbi;454183 Wrote: -- bobbiI have a workbook with 30 worksheets labeled Abook1 to Abook10, Bbook1 to Bbook10, Cbook1 to Cbook10. The worksheet name is repeated in the same cell on each sheet. I would like to save each group of 10 into seperate workbooks named A, B, and C (using only the first digit of each sheet name). Then make sure each workbook has the sheets in numerical order (they won't necessarily be completed in order). Is there a macro that will do all that automatically? -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'copy sheets - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=125723) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125723 |
copy sheets
hanks so much-most helpfull!
bobbi "Simon Lloyd" wrote: This should do what you need: Code: -------------------- Sub move_n_group_sheets() Dim Arr As Varriant, a As Long Arr = Array("A", "B", "C") For a = 0 To 2 Sheets(Array(Arr(a) & "Book1", Arr(a) & "Book2", Arr(a) & "Book3", _ Arr(a) & "Book4", Arr(a) & "Book5", Arr(a) & "Book6", Arr(a) & _ "Book7", Arr(a) & "Book8", Arr(a) & "Book9", Arr(a) & "Book10")).Move ActiveWorkbook.SaveAs (Arr(a) & ".xls") ActiveWorkbook.Close False Next a End Sub -------------------- bobbi;454210 Wrote: Thank you for your response. I suppose I wasn't very clear. I want to direct each group into a separate workbook for each group (A, B and C). The full worksheet name can be used. -- bobbi "Simon Lloyd" wrote: What do you mean "Using only the first digit of each sheet", that would give you problems as you cannot have more than one sheet named the same! bobbi;454183 Wrote: -- bobbiI have a workbook with 30 worksheets labeled Abook1 to Abook10, Bbook1 to Bbook10, Cbook1 to Cbook10. The worksheet name is repeated in the same cell on each sheet. I would like to save each group of 10 into seperate workbooks named A, B, and C (using only the first digit of each sheet name). Then make sure each workbook has the sheets in numerical order (they won't necessarily be completed in order). Is there a macro that will do all that automatically? -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'copy sheets - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=125723) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125723 |
| All times are GMT +1. The time now is 10:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com