Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets to a different workbook
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets to a different workbook
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. -- Gary''s Student - gsnu200832 "Angel C" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets to a different workbook
Yes, I am renaming each sheet to unique name. I think my issue may be around
opening the new workbook. Ugh! "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200832 "Angel C" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets to a different workbook
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 Befo=Workbooks("new_one.xls").Sheets(1) Next End Sub -- Gary''s Student - gsnu200832 "Angel C" wrote: Yes, I am renaming each sheet to unique name. I think my issue may be around opening the new workbook. Ugh! "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200832 "Angel C" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets to a different workbook
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. -- Many Thanks Sue "Gary''s Student" wrote: 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 Befo=Workbooks("new_one.xls").Sheets(1) Next End Sub -- Gary''s Student - gsnu200832 "Angel C" wrote: Yes, I am renaming each sheet to unique name. I think my issue may be around opening the new workbook. Ugh! "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200832 "Angel C" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets to a different workbook
Here is just a start:
Sub hfdsakf() Dim MyPath As String MyPath = CreateObject("WScript.Shell").Specialfolders("Desk Top") 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 -- Gary''s Student - gsnu200832 "Sue" wrote: 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. -- Many Thanks Sue "Gary''s Student" wrote: 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 Befo=Workbooks("new_one.xls").Sheets(1) Next End Sub -- Gary''s Student - gsnu200832 "Angel C" wrote: Yes, I am renaming each sheet to unique name. I think my issue may be around opening the new workbook. Ugh! "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200832 "Angel C" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheets within a workbook | Excel Discussion (Misc queries) | |||
Copy Worksheets Into Master Workbook | Excel Programming | |||
Copy worksheets to another workbook | Excel Programming | |||
Copy Worksheets from one Workbook to Another | Excel Worksheet Functions | |||
Copy four worksheets from one workbook into a new workbook.e-mail | Excel Programming |