copy sheets

[bobbi]

--
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]

What do you mean "Using only the first digit of each sheet", that woul
give you problems as you cannot have more than one sheet named the same

-
bobbiI have a workbook with 30 worksheets labeled Abook1 to Abook10
Bbook
to Bbook10, Cbook1 to Cbook10. Th

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 eac

sheet name). Then make sure each workbook has the sheets in numerica
orde
(they won't necessarily b

completed in order). Is there a macro that will do all tha
automatically

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[bobbi]

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.

 

[Simon Lloyd]

This should do what you need

Code
-------------------
Sub move_n_group_sheets(
Dim Arr As Varriant, a As Lon
Arr = Array("A", "B", "C"
For a = 0 To
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")).Mov
ActiveWorkbook.SaveAs (Arr(a) & ".xls"
ActiveWorkbook.Close Fals
Next
End Su
-------------------

Thank you for your response

I suppose I wasn't very clear. I want to direct each group into
separat
workbook for each group (A, B and C). The full worksheet name can b
used
-
bobb





Microsoft Office Discussion' (http://www.thecodecage.com)

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[bobbi]

hanks so much-most helpfull!

bobbi

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
--------------------





--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)