| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
| Copy Two Visible Ranges (From Two Sheets) To A New Workbook (AlsoWith Two Sheets) | Excel Programming | |||
| Copy Two Visible Ranges (From Two Sheets) To A New Workbook (AlsoWith Two Sheets) | Excel Programming | |||
| Create New WB with copy of Sheets 1 & 2 when sheets count reaches 100 | Excel Programming | |||
| in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Linear Mode
