# Efficient ways to copy row based on condition

#### srosk

##### Board Regular
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!!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### My Aswer Is This

##### Well-known Member
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
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
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
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
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
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
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
Re: Code only copies one column

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

#### srosk

##### Board Regular
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``````

1,102,843
Messages
5,489,208
Members
407,679
Latest member
Elaine Grass

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...