Copy Data where column F = Yes

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,730
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!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Twollaston

Board Regular
Joined
May 24, 2019
Messages
233
Can you send a sample of your data and I can make it for you

How many rows of data are there usually?
 
Last edited:

Twollaston

Board Regular
Joined
May 24, 2019
Messages
233
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
Code:
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,730
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.
 
Last edited:

Twollaston

Board Regular
Joined
May 24, 2019
Messages
233
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,856
Messages
5,471,145
Members
406,747
Latest member
smstrickland

This Week's Hot Topics

Top