Type a number and loop through cells and copy if condition is met

[marlenehm]

Hi All,

I have a master data sheet which contains item numbers with different components. Each item has many different components. There are therefore more than one line with the same item number but different components.

I would like to be able to type in a number in D2 for example in a new sheet and then it will copy all the lines which match with this article number from the master data sheet and paste it into B13 for example.

I get the error "invalid use of property", does anyone know how come?
I'm quite new to this and am trying to experiment a bit.

Sub Button2_Click()
a = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row


For i = 13 To a


If Worksheets("data").Cells(i, 3).Value = "D2" Then
Worksheets("data").Rows(i).Copy
Worksheets("master").Activate
b = Worksheets("master").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("master").Cells(B13).Select
Activatesheet.Paste
Worksheets("data").Activate

End If


Next


Application.CutCopyMode = False


ThisWorkbook.Worksheets ("data"), Cells(1, 1).Select


End Sub

 

[Fluff]

Try

Worksheets("master").[COLOR=#ff0000]Range("B13")[/COLOR].Select

 

[marlenehm]

Hi,
It still doesn't work. It is this part in bold it marks: ThisWorkbook.Worksheets ("data"), Cells(1, 1).Select

 

[Fluff]

Should be a period before cells not a comma

ThisWorkbook.Worksheets("data").Cells(1, 1).Select

 

[marlenehm]

Hi Fluff,

Thank you for trying to help,
I receive this error now "Object required" at activatesheet.paste


Sub Button3_Click()
a = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row


For i = 13 To a


If Worksheets("data").Cells(i, 1).Value = Range("D2") Then
Worksheets("data").Rows(i).Copy
Worksheets("master").Activate
b = Worksheets("master").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("master").Range(B13).Select
Activatesheet.Paste
Worksheets("data").Activate

End If


Next


Application.CutCopyMode = False


ThisWorkbook.Worksheets("data").Cells(1, 1).Select




End Sub

 

[Stiuart_W]

Number of issues here.

You are copying an entire row of data and then attempting to paste it into a single cell which isn't possible. Also it doesn't make sense to start in column B and paste the entire row as it will try to paste to "No of columns" +1 which obviously doesn't make sense either. Also B13 should be "B13" - I've changed it to A13 due to my first comment

Sub Button3_Click()
 a = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row

 For i = 13 To a
    If Worksheets("data").Cells(i, 1).Value = Range("D2") Then
        Worksheets("data").Rows(i).EntireRow.Copy
        
        Worksheets("master").Activate
        b = Worksheets("master").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("master").Range("A13").Select
        ActiveSheet.Paste
        Worksheets("data").Activate
    End If
 Next

 Application.CutCopyMode = False

 ThisWorkbook.Worksheets("data").Cells(1, 1).Select
 End Sub

 

[Fluff]

My last post here seems to have disappeared.

That line should be ActiveSheet.Paste, but it will still fail as you cannot paste an entire row, starting in col B. try

Sub Button2_Click()
    a = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row


    For i = 13 To a
        If Worksheets("data").Cells(i, 3).Value = Range("D2") Then
            Worksheets("data").Rows(i).Copy
            Worksheets("master").Activate
            b = Worksheets("master").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("master").Range("A13").Select
            ActiveSheet.Paste
            Worksheets("data").Activate
        End If
    Next
    
    
    Application.CutCopyMode = False
    
    ThisWorkbook.Worksheets("data").Cells(1, 1).Select

End Sub

 

[marlenehm]

Hi both,

Thank you, both of them are working.

I only have one problem left now (I hope). How do I correct it so I can copy more than one row?

I would like to copy all the rows in my data sheet that have the value in "D2" in column A.

Also thank you very much Stiuart_W for explaining me how come my code does not work. I'm learning something.

 

[marlenehm]

Actually no sorry Fluff, it's only Stiuart_W's code that is working.

 

[Stiuart_W]

Ok. This will do what you are after

Sub Button3_Click()
    LastRowDataSheet = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row
    LastRowMasterSheet = Worksheets("master").Cells(Rows.Count, 1).End(xlUp).Row
    If LastRowMasterSheet < 13 Then
        LastRowMasterSheet = 13
    Else
        LastRowMasterSheet = LastRowMasterSheet + 1
    End If
 
 
    For i = 13 To LastRowDataSheet
        If Worksheets("data").Cells(i, 1).Value = Worksheets("master").Cells(2, 4).Value Then
            Worksheets("data").Rows(i).EntireRow.Copy
            Worksheets("master").Activate
            Worksheets("master").Rows(LastRowMasterSheet).Select
            ActiveSheet.Paste
            LastRowMasterSheet = LastRowMasterSheet + 1
        End If
    Next i
    Application.CutCopyMode = False
    Worksheets("data").Activate
    ThisWorkbook.Worksheets("data").Cells(1, 1).Select
 End Sub