Copy worksheets to a different workbook

[Angel C]

Hi, I am trying to copy a worksheet multiple times from an open workbook to
new workbook. Each copy of the worksheet would contain different data based
on changing one cell (the worksheet has sumif() formulas that populate it
based on the changed cell). I would like to replicate the worksheet copy
functionality so that all formatting is retained. I recorded a macro but for
some reason when I implementent the code, it does not work. I keep getting
subscript out of range. Help please!!

Thanks!

 

[Gary''s Student]

Without seeing your code, just remember that if you make mulitple copies of
the same worksheet in a new workbook, each copy must have a unique name.

 

[Angel C]

Yes, I am renaming each sheet to unique name. I think my issue may be around
opening the new workbook. Ugh!

 

[Gary''s Student]

This creates a new workbook. It copies the first sheet of the old workbook
to the new one several times, each with a different name.

Once you have the copies, go to them and customize the cells:

Sub angellic()
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="new_one.xls"
newname = Array("alpha", "beta", "gamma")
For i = 0 To 2
Windows("Book1.xls").Activate
Sheets(1).Select
ActiveSheet.Name = newname(i)
ActiveSheet.Copy Before:=Workbooks("new_one.xls").Sheets(1)
Next
End Sub

 

[Sue]

Hi

I have been reading and trying this code - however my problem is the sheets
I want to automatically copy are in a folder on the desktop named 'Card' it
has 20 separate sheets named OB1, OB2, OB3 etc can this code be adapted to to
open a new workbook say named 'Sue' and place all the sheets in the correct
tab order.

 

[Gary''s Student]

Here is just a start:

Sub hfdsakf()
Dim MyPath As String
MyPath = CreateObject("WScript.Shell").Specialfolders("DeskTop")
MyPath = MyPath & "\Card\"
ChDir MyPath
ActiveWorkbook.SaveAs Filename:=MyPath & "Sue.xls"
nobs = 20
For n = 1 To nobs
fname = "OB" & n & ".xls"
MsgBox (MyPath & fname)
Workbooks.Open Filename:=MyPath & fname
'''''''''''''''''''''''''''''''''''''''
'
' add code to copy the worksheets
' and close the OBs
'
'''''''''''''''''''''''''''''''''''''''
Next
ActiveWorkbook.SaveAs Filename:=MyPath & "Sue.xls"
MsgBox ("done")
End Sub


It will get you to the folder and loop thru the OBs. You will need to add
code to copy the sheets and close the OBs