Copy two sheets from one workbook to another

[UHsoccer]

I have two worksheets open and I assume that the one with the VB code, has the property "ThisWorkbook"
I want to copy two sheets from one to the other, then I want to move the copied sheets to the end

Here is what I tried
There is an Error in step 3 "OBJECT Required"
Step 4 is incomplete, how do I define "The LAST sheet"?

-----------------------

Dim wb As Workbook
Dim wbTO, wbFROM As String

' 1 there are two workbook, "ThisWorkBook" has the VB code
For Each wb In Workbooks
If wb.Name = ThisWorkbook.Name Then wbFROM = wb.Name Else wbTO = wb.Name
Next wb

' 2 select the sheets on the FROM workbook
Workbooks(wbFROM).Activate
Sheets(Array("Order Info", "Customer Quote")).Select

' 3 Copy array of sheets to the "TO workbook" ------- ERROR - Object required
Sheets(Array("Order Info", "Customer Quote")).Copy Before:=Workbooks(wbTO.Sheets(1))

' 4 Now move the copied sheets to the end - LAST is to be determined
Sheets(Array("Order Info", "Customer Quote")).Move After:=Sheets(LAST)

 

[SydneyGeek]

I have two worksheets open and I assume that the one with the VB code, has the property "ThisWorkbook"
I want to copy two sheets from one to the other, then I want to move the copied sheets to the end

Here is what I tried
There is an Error in step 3 "OBJECT Required"
Step 4 is incomplete, how do I define "The LAST sheet"?

-----------------------

Dim wb As Workbook
Dim wbTO, wbFROM As String

' 1 there are two workbook, "ThisWorkBook" has the VB code
For Each wb In Workbooks
If wb.Name = ThisWorkbook.Name Then wbFROM = wb.Name Else wbTO = wb.Name
Next wb

' 2 select the sheets on the FROM workbook
Workbooks(wbFROM).Activate
Sheets(Array("Order Info", "Customer Quote")).Select

' 3 Copy array of sheets to the "TO workbook" ------- ERROR - Object required
Sheets(Array("Order Info", "Customer Quote")).Copy Before:=Workbooks(wbTO.Sheets(1))

' 4 Now move the copied sheets to the end - LAST is to be determined
Sheets(Array("Order Info", "Customer Quote")).Move After:=Sheets(LAST)

My guess is that you have a syntax error.
Change this...
Sheets(Array("Order Info", "Customer Quote")).Copy Before:=Workbooks(wbTO.Sheets(1))

to this...
Sheets(Array("Order Info", "Customer Quote")).Copy Before:=Workbooks(wbTO).Sheets(1)

and try again.

Denis

 

[UHsoccer]

Thanks, that fixed problem number 1 (item 3)

Any thoughts on item 4: Move the two copied sheets into the last position?

 

[SydneyGeek]

' 4 Now move the copied sheets to the end - LAST is to be determined
Sheets(Array("Order Info", "Customer Quote")).Move After:=Sheets(LAST)

Try
Sheets(Array("Order Info", "Customer Quote")).Move After:=Sheets(wbTO.Sheets.Count)

You might also try combining 3 and 4:
Sheets(Array("Order Info", "Customer Quote")).Copy After:=Workbooks(wbTO).Sheets.Count

Denis

 

[UHsoccer]

Using the first option
Sheets(Array("Order Info", "Customer Quote")).Move After:=Sheets(wbTO.Sheets.Count)

Error : Object required

Combining the copy and move into one
Sheets(Array("Order Info", "Customer Quote")).Copy After:=Workbooks(wbTO).Sheets.Count

Error : Copy method of Sheets Class failed

Thanks for your help SydneyGeek

 

[SydneyGeek]

Hi, try this --

Workbooks(wbTo).Activate
Sheets(Array("Order Info", "Customer Quote")).Move After:=Sheets(ActiveWorkbook.Sheets.Count)

Worked for me

Denis

 

[UHsoccer]

Great, that one works. Thanks for your help

 

[bradley7]

this work great but is there any way to do it with only copy the values and not the formulas

 

[SydneyGeek]

Hi Brad,

Do you want the formatting too, or just the values?

There are a couple of approaches you could use:
1. For each sheet copied across, do this:

Range(Cells(1,1), Cells(1,1).SpecialCells(xlLastCell)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

You could put it in a loop to run through ALL sheets in the new book:

For Each Sht in ActiveWorkbook.Sheets
Sht.Activate
Range(Cells(1,1), Cells(1,1).SpecialCells(xlLastCell)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Next Sht

Denis

 

[bradley7]

using this way to copy multi-sheets, how can i copy only the values and formats

Workbooks.Add
Dim wb As Workbook
Dim wbTo, wbFrom As String
For Each wb In Workbooks
If wb.Name = ThisWorkbook.Name Then _
wbFrom = wb.Name Else wbTo = wb.Name
Next wb

Workbooks(wbFrom).Activate
Sheets(Array("40.25 to 50", "Greater Than 50 Hours")).Select
Sheets(Array("40.25 to 50", "Greater Than 50 Hours")).Copy _
Before:=Workbooks(wbTo).Sheets(1)