| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create new workbook, temporarily name it and copy sheets to it
My VBA is basic to say the least however I am learning from my mistakes
pretty quick! :o) I'm trying to copy some sheets from an existing workbook to a new workbook in their entirety i.e. page breaks, colours etc. The error occurs as I do not know how to rename the newly opened workbook and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc. I've looked at previous questions on this topic and am a little confused so would appreciate some clarity... Below is an extract from the macro if it helps Many thanks in advance Jim Workbooks.Add Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls" Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("Existing Data").Select Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("4. All Sales Execs Report").Select Sheets("4. All Sales Execs Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("3. Sales Exec Report").Select Sheets("3. Sales Exec Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create new workbook, temporarily name it and copy sheets to it
Hi Jim,
Try: '============= Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls") Set WB2 = Workbooks.Add WB1.Sheets("Existing Data").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) End Sub '<<============= --- Regards, Norman "DerbyJim" wrote in message ... My VBA is basic to say the least however I am learning from my mistakes pretty quick! :o) I'm trying to copy some sheets from an existing workbook to a new workbook in their entirety i.e. page breaks, colours etc. The error occurs as I do not know how to rename the newly opened workbook and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc. I've looked at previous questions on this topic and am a little confused so would appreciate some clarity... Below is an extract from the macro if it helps Many thanks in advance Jim Workbooks.Add Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls" Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("Existing Data").Select Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("4. All Sales Execs Report").Select Sheets("4. All Sales Execs Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("3. Sales Exec Report").Select Sheets("3. Sales Exec Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create new workbook, temporarily name it and copy sheets to it
Thanks Norman.
I tried what you said and it actually made some sense to me... however I am now getting an Expected End Sub error, is it something to do with the Public Sub tester? The full code is as follows: Sub Run_Extract() ' ' Run_Extract Macro ' Output an extract based on current information ' ' Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("FS Complaints Report - All.xls") Set WB2 = Workbooks.Add WB1.Sheets("XXXX Extract").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("2. Venue Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("0. Summary Report").Copy _ Befo=WB2.Sheets(4) End Sub "Norman Jones" wrote: Hi Jim, Try: '============= Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls") Set WB2 = Workbooks.Add WB1.Sheets("Existing Data").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) End Sub '<<============= --- Regards, Norman "DerbyJim" wrote in message ... My VBA is basic to say the least however I am learning from my mistakes pretty quick! :o) I'm trying to copy some sheets from an existing workbook to a new workbook in their entirety i.e. page breaks, colours etc. The error occurs as I do not know how to rename the newly opened workbook and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc. I've looked at previous questions on this topic and am a little confused so would appreciate some clarity... Below is an extract from the macro if it helps Many thanks in advance Jim Workbooks.Add Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls" Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("Existing Data").Select Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("4. All Sales Execs Report").Select Sheets("4. All Sales Execs Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("3. Sales Exec Report").Select Sheets("3. Sales Exec Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create new workbook, temporarily name it and copy sheets to it
Hi Jim,
Delete all the lines befo Public Sub Tester() However, by all means change the name of the sub from Tester to a a name of your choice. --- Regards, Norman "DerbyJim" wrote in message ... Thanks Norman. I tried what you said and it actually made some sense to me... however I am now getting an Expected End Sub error, is it something to do with the Public Sub tester? The full code is as follows: Sub Run_Extract() ' ' Run_Extract Macro ' Output an extract based on current information ' ' Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("FS Complaints Report - All.xls") Set WB2 = Workbooks.Add WB1.Sheets("XXXX Extract").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("2. Venue Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("0. Summary Report").Copy _ Befo=WB2.Sheets(4) End Sub "Norman Jones" wrote: Hi Jim, Try: '============= Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls") Set WB2 = Workbooks.Add WB1.Sheets("Existing Data").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) End Sub '<<============= --- Regards, Norman "DerbyJim" wrote in message ... My VBA is basic to say the least however I am learning from my mistakes pretty quick! :o) I'm trying to copy some sheets from an existing workbook to a new workbook in their entirety i.e. page breaks, colours etc. The error occurs as I do not know how to rename the newly opened workbook and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc. I've looked at previous questions on this topic and am a little confused so would appreciate some clarity... Below is an extract from the macro if it helps Many thanks in advance Jim Workbooks.Add Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls" Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("Existing Data").Select Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("4. All Sales Execs Report").Select Sheets("4. All Sales Execs Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("3. Sales Exec Report").Select Sheets("3. Sales Exec Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create new workbook, temporarily name it and copy sheets to it
Now working, thanks Norman!!
"Norman Jones" wrote: Hi Jim, Delete all the lines befo Public Sub Tester() However, by all means change the name of the sub from Tester to a a name of your choice. --- Regards, Norman "DerbyJim" wrote in message ... Thanks Norman. I tried what you said and it actually made some sense to me... however I am now getting an Expected End Sub error, is it something to do with the Public Sub tester? The full code is as follows: Sub Run_Extract() ' ' Run_Extract Macro ' Output an extract based on current information ' ' Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("FS Complaints Report - All.xls") Set WB2 = Workbooks.Add WB1.Sheets("XXXX Extract").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("2. Venue Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("0. Summary Report").Copy _ Befo=WB2.Sheets(4) End Sub "Norman Jones" wrote: Hi Jim, Try: '============= Public Sub Tester() Dim WB1 As Workbook Dim WB2 As Workbook Set WB1 = Workbooks("EXISTING NAMED WORKBOOK.xls") Set WB2 = Workbooks.Add WB1.Sheets("Existing Data").Copy _ After:=WB2.Sheets(3) WB1.Sheets("4. All Sales Execs Report").Copy _ Befo=WB2.Sheets(4) WB1.Sheets("3. Sales Exec Report").Copy _ Befo=WB2.Sheets(4) End Sub '<<============= --- Regards, Norman "DerbyJim" wrote in message ... My VBA is basic to say the least however I am learning from my mistakes pretty quick! :o) I'm trying to copy some sheets from an existing workbook to a new workbook in their entirety i.e. page breaks, colours etc. The error occurs as I do not know how to rename the newly opened workbook and it comes unstuck when it refers back to Book1 or Book2 or Book3 etc. I've looked at previous questions on this topic and am a little confused so would appreciate some clarity... Below is an extract from the macro if it helps Many thanks in advance Jim Workbooks.Add Windows.CompareSideBySideWith "EXISTING NAMED WORKBOOK.xls" Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("Existing Data").Select Sheets("Existing Data").Copy After:=Workbooks("Book2").Sheets(3) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("4. All Sales Execs Report").Select Sheets("4. All Sales Execs Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate Sheets("3. Sales Exec Report").Select Sheets("3. Sales Exec Report").Copy Befo=Workbooks("Book2").Sheets(4) Windows("EXISTING NAMED WORKBOOK.xls").Activate |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to create new workbook and sheets | Excel Discussion (Misc queries) | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| create workbook, copy sheets | Excel Programming | |||
| Create New Workbook - Name book - 4 Sheets - Name Sheets | Excel Programming | |||
| VBA to create an Array of all Sheets in workbook | Excel Programming | |||
Linear Mode
