Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Efficient ways to copy row based on condition

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Efficient ways to copy row based on condition

    I have data stored on main sheet [Sheet1]. There are 4 other sheets on the spreadsheet. [Sheet 2] [Sheet 3] [Sheet 4] [Sheet 5]

    I would like to copy the header (row 1) and data to copy to Sheet 2 from Sheet 1 if Sheet 1 column AA = 1; to Sheet 3 if Sheet 1 AB = 1, to Sheet 3 if Sheet 1 AC = 1, and to Sheet 4 if Sheet 1 AD =1.

    I have code to delete specific columns for individual sheets... but I need to somehow get the data there. I read something about auto filter, but do not know how to apply.

    Looking for the most efficient way to do this. Thank you!!

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    14,081
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Efficient ways to copy row based on condition

    Are you saying if any cell in column AA do this for example?
    You Said AA so I assume you mean any cell in column AA and the other columns also.

    Now I maybe could provide a script. But if you need the Most efficient.
    I probable could not do that. There are a lot of other people on this forum who may be able to do the Most efficient code.

    I will continue to monitor this thread
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,235
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Efficient ways to copy row based on condition

    I don't know about most efficient, but try....
    Code:
    Sub MM1()
    Application.ScreenUpdating = False
    For x = 1 To 4
    With Columns(x + 26)
        .AutoFilter field:=1, Criteria1:=1
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet" & x + 1).Range("A1")
        .AutoFilter
    End With
    Next x
    Application.ScreenUpdating = True
    End Sub
    Last edited by Michael M; Oct 3rd, 2018 at 12:55 AM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Efficient ways to copy row based on condition

    It would be on a per row basis.

    So if AA2 = 1, copy row to Sheet 2.. if AB2=1, copy row to Sheet 3.. if AB3 = blank, do nothing.. if AB4 = 1 copy to Sheet 3.

    The way my script works now, is it copies everything from Sheet 1 to Sheet 2, 3, 4, and 5. From there, based on AA/AB/AC/AD value, it will delete any row where the specific cell <>1. Current state works, however... I will sometimes have over 100k records, and it takes a significant amount of time to run > 25 minutes. I think due to repeating the 'loop' process on so many sheets. Thank you for your help!

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Efficient ways to copy row based on condition

    Thank you! So the sheet names aren't actually Sheet 1/2/3, but instead something like 'Data Report' (main feed), Date Data, Address Data, etc. How can I work that into the code?

    Can you let me know if I understand correctly? Currently what you wrote will look at x 1-4. When X=1, it will filter column AB looking for 1, and copy the rows. Then copy to Sheet 2. X=2, AC / Sheet 3. Where it says .SpecialCells, would I just update to:

    Code:
    If X = 1 Then .SpecialCells(xlCellTypeVisible).Copy Sheets("Date Dta").Range("A1")
    Thank you all for your help... what a great resource and learning opportunity!

    Quote Originally Posted by Michael M View Post
    I don't know about most efficient, but try....
    Code:
    Sub MM1()
    Application.ScreenUpdating = False
    For x = 1 To 4
    With Columns(x + 26)
        .AutoFilter field:=1, Criteria1:=1
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet" & x + 1).Range("A1")
        .AutoFilter
    End With
    Next x
    Application.ScreenUpdating = True
    End Sub

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    14,081
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Efficient ways to copy row based on condition

    You said:
    It would be on a per row basis.

    So if AA2 = 1, copy row to Sheet 2.. if AB2=1, copy row to Sheet 3.. if AB3 = blank, do nothing.. if AB4 = 1 copy to Sheet 3.

    AA2 Means Column AA Row 2
    AB2 Means Column AB Row 2

    So this seems to me to always be the same row but different columns

    If that what you mean.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Sep 2018
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Efficient ways to copy row based on condition

    This is working but only copies value in column AA / AB / AC, etc. Is it possible to copy all columns with a value over.. ie A:AZ? Thank you!

    Quote Originally Posted by Michael M View Post
    I don't know about most efficient, but try....
    Code:
    Sub MM1()
    Application.ScreenUpdating = False
    For x = 1 To 4
    With Columns(x + 26)
        .AutoFilter field:=1, Criteria1:=1
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet" & x + 1).Range("A1")
        .AutoFilter
    End With
    Next x
    Application.ScreenUpdating = True
    End Sub

  8. #8
    Board Regular
    Join Date
    Sep 2018
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Code only copies one column

    Hi there. I have the following code, but it only copies column AA / AB / AC depending on the value of X. How do I get it to copy all columns in the row?

    Code:
    Sub MM1()
    Application.ScreenUpdating = False
    For x = 1 To 4
    With Columns(x + 26)
        .AutoFilter field:=1, Criteria1:=1
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet" & x + 1).Range("A1")
        .AutoFilter
    End With
    Next x
    Application.ScreenUpdating = True
    End Sub
    Thanks!

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    16,071
    Post Thanks / Like
    Mentioned
    278 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Code only copies one column

    Try
    Code:
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet" & x + 1).Range("A1")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    Board Regular
    Join Date
    Sep 2018
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code only copies one column

    Thanks. I had to change the original code, because the sheet names will be different. So I tried your code and it says Invalid or Unqualified reference. When I remove the period.. the sub is undefined...

    Thoughts?

    Code:
    Sub MM1()
    Application.ScreenUpdating = False
        Selection.AutoFilter Field:=(27), Criteria1:="1"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Vesting").Range("A1")
    Application.ScreenUpdating = True
    End Sub

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
  •