Create new workbook, temporarily name it and copy sheets to it

[DerbyJim]

My VBA is basic to say the least however I am learning from my mistakes
pretty quick! )

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
Before:=Workbooks("Book2").Sheets(4)
Windows("EXISTING NAMED WORKBOOK.xls").Activate
Sheets("3. Sales Exec Report").Select
Sheets("3. Sales Exec Report").Copy Before:=Workbooks("Book2").Sheets(4)
Windows("EXISTING NAMED WORKBOOK.xls").Activate

 

[Norman Jones]

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 _
Before:=WB2.Sheets(4)
WB1.Sheets("3. Sales Exec Report").Copy _
Before:=WB2.Sheets(4)
End Sub
'<<=============

 

[DerbyJim]

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 _
Before:=WB2.Sheets(4)
WB1.Sheets("3. Sales Exec Report").Copy _
Before:=WB2.Sheets(4)
WB1.Sheets("2. Venue Report").Copy _
Before:=WB2.Sheets(4)
WB1.Sheets("0. Summary Report").Copy _
Before:=WB2.Sheets(4)

End Sub

 

[Norman Jones]

Hi Jim,

Delete all the lines before:

Public Sub Tester()

However, by all means change the name of the sub from Tester to a a name of
your choice.

 

[DerbyJim]

Now working, thanks Norman!!

Hi Jim,

Delete all the lines before:


However, by all means change the name of the sub from Tester to a a name of
your choice.