Results 1 to 5 of 5

Thread: Copy Data where column F = Yes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Data where column F = Yes

    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!
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    Board Regular
    Join Date
    May 2019
    Posts
    180
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data where column F = Yes

    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 by Twollaston; Jun 12th, 2019 at 04:07 PM.

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    180
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data where column F = Yes

    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

  4. #4
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data where column F = Yes

    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 by gheyman; Jun 12th, 2019 at 05:09 PM.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  5. #5
    Board Regular
    Join Date
    May 2019
    Posts
    180
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data where column F = Yes

    Quote Originally Posted by gheyman View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •