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

M

marlenehm

New Member
Joined
Oct 23, 2017
Messages
5
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Try
Code: 
Worksheets("master").[COLOR=#ff0000]Range("B13")[/COLOR].Select
 
Upvote 0
Hi,
It still doesn't work. It is this part in bold it marks: ThisWorkbook.Worksheets ("data"), Cells(1, 1).Select
 
Upvote 0
Should be a period before cells not a comma
Code: 
ThisWorkbook.Worksheets("data").Cells(1, 1).Select
 
Upvote 0
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
 
Upvote 0
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

Code: 
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
 
Upvote 0
:confused: 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
Code: 
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
Click to expand...
 
Upvote 0
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.


 
Upvote 0
Ok. This will do what you are after

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
VBA
Replies
6
Views
187
twildone
T
H
  • Solved
Consolidate data from selected sheets into master sheet & automatically update data.
Replies
8
Views
687
Joe4
Joe4
P
  • Question
VBA issue. runtime 91 - only occurring after change to Range?
Replies
6
Views
201
PTEOMARK
P
N
  • Solved
Copy/paste a row from within a loop, before that loop moves on to next cell
Replies
5
Views
239
Joe4
Joe4
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
153
harmless92
H

Forum statistics

Threads
1,214,983
Messages
6,122,595
Members
449,089
Latest member
Motoracer88

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