Copy and Paste Error : '1004'

Question

I am using VBA to help manage a set of data. I will have Monthly Data for 50 months and I wish to categorize it into different sheets based on the FIRST word within a cell. Here is what I done so far;

I created a workbook with 2 sheets,

Sheet1(Employee Inventory)
Sheet2(PB)

and my code is written and saved in this Workbook.

Sub myCode()
    Dim OldString As String
    Dim NewString As String
    Set i = Sheets("Employee Inventory")
    Set PB = Sheets("PB")
    Dim counterPB
    counterPB = 2
    Dim d
    Dim j
    d = 1
    j = 2
    Do Until IsEmpty(i.Range("D" & j))
        OldString = i.Range("D" & j)
        NewString = Left(OldString, 2)
        If NewString = "PB" Then
            i.Rows(j).EntireRow.Copy
            PB.Range("A" & counterPB).Select
            PB.Paste
            counterPB = counterPB + 1
        End If
        j = j + 1
    Loop
End Sub

Apologies for the code as it looks weird. This code looks at Sheet1 and scans column "D" and looks for the first word starting with "PB". Once it does find it, it will copy and paste the whole row into another sheet called Sheet2(PB).

When I am in Microsoft Visual Basic window AND I have the Excel Spreadsheet with Sheet1(Employee Inventory) tab opened and when I click Run Sub I get the following error: Run-time error '1004': Application-defined or object-defined error. When I click on "PB" tab, nothing is being copy and pasted in there.

HOWEVER, when I click on the PB tab and then I click Run Sub, the codes executes and any rows containing the first word "PB" will be copied and pasted in the "PB" tab.

My question is, why does it only work when I have the Sheet2 opened and not when I have Sheet1 Opened?

Fadi Fadi · Accepted Answer · 2016-01-07T02:15:49

when use range.select its parent worksheet must be selected, so we can use PB.Activate or not use .select at all.

Try to replace this:

i.Rows(j).EntireRow.Copy
PB.Range("A" & counterPB).Select
PB.Paste

with this line:

i.Rows(j).Copy PB.Rows(counterPB)

Copy and Paste Error : '1004'

2 Answers