Efficient ways to copy row based on condition

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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!!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,942
Office Version
2013
Platform
Windows
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:

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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!
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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!

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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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.
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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!

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
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,377
Office Version
365
Platform
Windows
Re: Code only copies one column

Try
Code:
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet" & x + 1).Range("A1")
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,851
Messages
5,483,306
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top