Copying row by row

Question

I need help copying values from one worksheet to another. Here is the code I'm using, but it won't work.

Sub UpdateLinks()
    Dim wbSource As Workbook
    Dim wbDestination As Workbook

    'open the source workbook and select the source sheet
    Set wbSource = Workbooks.Open( _
        Filename:="C:\Users\B48184\Desktop\Losses_Breakdown_by_OEE.xls")

    'Set the destition workbook variable
    Set wbDestination = Workbooks("TryVB.xlsm")

    wbSource.Sheets("Report").Range(Cells(4, 3), Cells(14, 8)).Copy
    wbDestination.Sheets("Sheet2").Range(Cells(4, 3), Cells(14, 8)).PasteSpecial (xlPasteAll)

    Application.CutCopyMode = False

    ActiveWorkbook.Save
End Sub

score 1 · Answer 1 · answered Sep 03 '14 at 05:50

The unqualified Cell(... 's refer to the active sheet, which may be different to the specified sheet, causing an error. Change to

With wbSource.Sheets("Report")
    .Range(.Cells(4, 3), .Cells(14, 8)).Copy
End With
With wbDestination.Sheets("Sheet2")
    .Range(.Cells(4, 3), .Cells(14, 8)).PasteSpecial xlPasteAll
End With

Note the .'s.

score -1 · Answer 2 · answered Sep 03 '14 at 05:30

-1

How about if you do this:

wbSource.Sheets("Report").Range(Cells(4, 3), Cells(14, 8)).Copy
wbDestination.Activate
Sheets("Sheet2").Select
Range(Cells(4, 3), Cells(14, 8)).PasteSpecial (xlPasteAll)

This should work.

answered Sep 03 '14 at 05:30

Grendizer

292
1
3
10

Copying row by row

2 Answers2