How do I Copy/Paste specific cells from other worksheet to current worksheet?

Question

I'm using this code to take a username an search for all of their associate info from multiple transactions. It should then paste them into the current worksheet. It seems to run, in that it doesn't throw up any errors and it executes the final "Select" command, but it doesn't return any pasted data.

Option Explicit
Sub InvestorReport()

Dim investorname As String
Dim finalrow As Integer
Dim i As Integer 'row counter

Sheets("Sheet1").Range("D6:K50").ClearContents

investorname = Sheets("Sheet1").Range("B3").Value
finalrow = Sheets("Investments").Range("I1000").End(xlUp).Row


For i = 2 To finalrow
    If Sheets("Investments").Cells(i, 1) = investorname Then
        MsgBox ("Works")
         Range(Cells(i, 2), Cells(i, 12)).Copy
         Sheets("Sheet1").Range("D100").End(xlUp).Offset(1, 0).PasteSpecial
        End If
Next i


Range("B3").Select

End Sub

I'd highly recommend not using `Copy` and `Paste` but instead just set the new range's values using the original range. Also, I believe the issue with your code is that you are not specifying the worksheet to copy from. Change the following line: `Range(Cells(i, 2), Cells(i, 12)).Copy` to `Sheets("Investments").Range(Sheets("Investments").Cells(i, 2), Sheets("Investments").Cells(i, 12)).Copy` — Jordan, Aug 08 '16 at 10:56
Why loop? Have you seen [This](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Aug 08 '16 at 10:58
The loop is because there may be multiple entries for a single username that I need to get information from. And the Msgbox was just me trying to test it, forgot to comment out/delete. — SWiM, Aug 08 '16 at 11:02
Jordan is right. You need to specify which worksheet you refer to in your .Copy command — Ulli Schmid, Aug 08 '16 at 11:04
Did you not see the link that I posted above :) Autofilter can find those multiple entries ;) — Siddharth Rout, Aug 08 '16 at 11:13

score 0 · Answer 1 · answered Aug 08 '16 at 11:31

this is the code to properly reference sheets:

Option Explicit

Sub InvestorReport()
    Dim investorname As String
    Dim finalrow As Long
    Dim i As Long 'row counter

    With Sheets("Sheet001") '<--| refer to "Sheet1" sheet
        .Range("D6:K50").ClearContents '<--| with every initial "dot" you keep referencing the object after the "With" keyword ("Sheet1" sheet, in this case)
        investorname = .Range("B3").value
    End With

    With Sheets("Investments") '<--| refer to "Investments" sheet
        finalrow = .Cells(.Rows.Count, "I").End(xlUp).row '<--| .Cells and .Rows refer to "Investments" sheet
        For i = 2 To finalrow
            If .Cells(i, 1) = investorname Then
                .Range(.Cells(i, 2), .Cells(i, 12)).Copy '<--| .Range and .Cells refer to "Investments" sheet
                Sheets("Sheet001").Range("D100").End(xlUp).Offset(1, 0).PasteSpecial '<--| here all references are explicitly made to "Sheet1" sheet
            End If
        Next i
    End With

    Sheets("Sheet001").Range("B3").Select
End Sub

and here follows the code to avoid loops and exploiting Autofilter:

Sub InvestorReport2()
    Dim investorname As String
    Dim finalrow As Long
    Dim i As Long 'row counter

    With Sheets("Sheet001") '<--| refer to "Sheet1" sheet
        .Range("D6:K50").ClearContents '<--| with every initial "dot" you keep referencing the object after the "With" keyword ("Sheet1" sheet, in this case)
        investorname = .Range("B3").value
    End With

    With Sheets("Investments") '<--| refer to "Investments" sheet
        With .Range("A1", .Cells(.Rows.Count, "L").End(xlUp)) '<--| refer to "Investments" sheet columns A to I fom row 1 (header) down to last non empty one in column I
            .AutoFilter field:=1, Criteria1:=investorname '<--| filter data on first column values matching "investorname"
            If Application.WorksheetFunction.Subtotal(103, .Cells.Resize(, 1)) > 1 Then '<--| if any cell has been filtered...
                .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).SpecialCells(xlCellTypeVisible).Copy '<--| ... copy filtered cells skipping headers and first column...
                Sheets("Sheet001").Range("D100").End(xlUp).Offset(1, 0).PasteSpecial '<--| ...and paste them
            End If
        End With
        .AutoFilterMode = False '<--| .. show all rows back...
    End With

    Sheets("Sheet001").Range("B3").Select
End Sub

@SWiM: did you get through it? – user3598756 Aug 23 '16 at 08:03 — user3598756, Aug 23 '16 at 08:03

How do I Copy/Paste specific cells from other worksheet to current worksheet?

1 Answers1