Copy two sheets from one workbook to another

U

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
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)
 
Sort by date Sort by votes
Hi Brad, did you try my suggestion?

1. Copy the sheets across
2. Once copied, run through the new workbook and convert formulas to values

Code: 
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

    'your code
    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)

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
hi, i will try, i wa confuss about the code, won't this copy the sheet to the new workbook and then do another copy that only copies the value and format.
ok, i will try it
 
Upvote 0
Hi Brad

Yes, that's what it will do. But that way you end up with all the correct column widths as well. Give it a go -- if it's too slow, there are a couple of other possibilities.

Denis
 
Upvote 0
YOu guess, another problem

This process create an array and thank Dennis, I understand but when I select another sheet straght after, for example, sheet('Another").select, there is a Object error which I think it is referring to the array. HOw do you ungroup the array.

Brad
 
Upvote 0
Hi Brad,

I tried a couple of different methods using the recorder, and re-ran it without problems. Basic syntax goes like this...
Code: 
Sub Macro1()
    'create the sheet array
    Sheets(Array("Sheet1", "Sheet3")).Select
    Sheets("Sheet1").Activate
    '... do stuff here
    'ungroup the sheets
    Sheets("Sheet3").Select
    'or...
    'Sheets("Sheet2").Select
End Sub
Could you post the version you're using, in case something else is tripping it up?

Denis
 
Upvote 0
i'm using 2002 at work and 2003 at home

Yes to ungroup I select another steets that wasn't in the group and it stop on the command with a , i think a object error, so maybe it i selected a sheet which is in the group, that might help

ta bud.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Cannot assign macro to button
2
Replies
10
Views
266
SPS41
S
I
  • Question Question
Value not shown in ComboBox1 when code run
Replies
1
Views
57
ipbr21054
I
R
  • Question Question
What a VBA code to copy table between 2 different workbooks
Replies
6
Views
166
Skyybot
Skyybot
I
  • Question Question
Find customer in column & link to his pdf
2
Replies
16
Views
642
NoSparks
N
I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
198
ipbr21054
I

Forum statistics

Threads
1,224,590
Messages
6,179,762
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top