Copy Data where column F = Yes

[gheyman]

I have a table and in the last column (F) there will be a Yes, No or it will be blank.

I want to loop through each row of the table (which starts on row 4) and if that row has a "Yes" in column F, I wan to copy that rows cells A, B, C, D and E
Past it in the next available Row in columns B, C, D, E and F
That tabs Name is CMOPUpload

The Table that its looping through is on a tab named ItemIDList (table name is ModelGeneral_vluItem)

Also on CMOPUpload it should start looking in Row 11 down for the next blank row to paste it in.

Any Help is very much appreciated, as always!

 

[Twollaston]

Can you send a sample of your data and I can make it for you

How many rows of data are there usually?

 

[Twollaston]

This might work for you. I'm not sure if you have data in cell B10 on CMOUpload, and also if B12 can be blank this might not work. If there is a always data in cells B11 and B12 or B10 and B11 then will work for either by changing Range("B11") (if b11 and b12 will have data) or Range("B10") (if rows b10 and b11 have dta)
Sorry I don't have much to go on. I tried to avoid the loop in case you have alot of data, but maybe a loop is best if b11 and/or b12 can be blank

Sub CopyRowss()


    Sheets("ItemIDList").Select




    ActiveSheet.Range("A4").AutoFilter Field:=6, Criteria1:="Yes", Operator:=xlFilterValues
    
    Range("A4").Select
    ActiveCell.Offset(1, 0).Select
    
    Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy
    
    Sheets("CMOUpload").Select
    Range("B11").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste


    Application.CutCopyMode = False
    Worksheets("ItemIDList").AutoFilterMode = False




End Sub

 

[gheyman]

Thanks! I will give this a try. It looks logical. I didn't think of filtering and then copying. I was thinking I needed to Loop through each line. B11 has a Title, so it will always have data.

 

[Twollaston]

Thanks! I will give this a try. It looks logical. I didn't think of filtering and then copying. I was thinking I needed to Loop through each line. B11 has a Title, so it will always have data.

Ok got it, this range in B11 is selected by choosing b11 and ctrl down, so that mean if b12 is empty it will go to the bottom of the sheet, we can put an if statement before that, something like
if B12 = 0 then and paste to b12
else do normal operation.

And yes I was deleting rows and copying rows with loops on a large spreadsheet and it almost brokedown my excel, but when i do it this way it takes a very short amount of time.