Code to move rows into another sheet based on certain values
Solved
jcbbach
vcoolio
- Posts
- 2
- Registration date
- Thursday October 7, 2021
- Status
- Member
- Last seen
- October 8, 2021
vcoolio
- Posts
- 1343
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 12, 2022
Related:
- Vba code to move rows to another sheet based on criteria
- Code to move rows into another sheet based on certain values ✓ - Forum - Excel
- Auto copy data from sheet1 to sheet based on criteria ✓ - Forum - Excel
- Copy rows to other sheets based on value in column ✓ - Forum - Excel
- Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
- Copy Rows from one sheet to others based on a condition ✓ - Forum - Office Software
1 reply
vcoolio
Updated on Oct 8, 2021 at 01:26 AM
- Posts
- 1343
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 12, 2022
Updated on Oct 8, 2021 at 01:26 AM
Hello jcbbach,
The following code should do both the tasks for you:-
I've assumed that your data starts in row2 with headings in row1.
I've also assumed that the criteria for patients who have passed screening tests to simply be "Passed" with details going to a sheet named "Passed".
You'll note that the above code is somewhat different to the one that you already have. It uses an array and the Autofilter which will make it much more efficient and faster on large data sets.
I've attached a sample file at the following link to show you how the code works:-
https://wetransfer.com/downloads/efe974a97408fe59b425bab7d52b26f520211008052121/1ef063
Click on the "RUN" button to see it work. You'll note that I have trimmed the sample file down to thirteen columns with the criteria in Column M but the code allows for your actual criteria column AH(34) so it should work as required. Please test the code in a copy of your workbook first.
I hope that this helps.
Cheerio,
vcoolio.
The following code should do both the tasks for you:-
Option Explicit Sub BachTest() Dim ar As Variant, i As Long, wsD As Worksheet Dim wsM As Worksheet: Set wsM = Sheets("Master") ar = [{"Failed","Failed","Failed","Passed";"Phone Screen - Failed", "Phone Screen - Not Interested", "Prescreen Fail","Passed"}] Application.ScreenUpdating = False For i = 1 To UBound(ar, 2) Set wsD = Sheets(ar(1, i)) With wsM.[A1].CurrentRegion .AutoFilter 34, ar(2, i) .Offset(1).EntireRow.Copy wsD.Range("A" & Rows.Count).End(3)(2) .Offset(1).EntireRow.Delete .AutoFilter wsD.Columns.AutoFit End With Next i Application.ScreenUpdating = True MsgBox "All done!", vbExclamation End Sub
I've assumed that your data starts in row2 with headings in row1.
I've also assumed that the criteria for patients who have passed screening tests to simply be "Passed" with details going to a sheet named "Passed".
You'll note that the above code is somewhat different to the one that you already have. It uses an array and the Autofilter which will make it much more efficient and faster on large data sets.
I've attached a sample file at the following link to show you how the code works:-
https://wetransfer.com/downloads/efe974a97408fe59b425bab7d52b26f520211008052121/1ef063
Click on the "RUN" button to see it work. You'll note that I have trimmed the sample file down to thirteen columns with the criteria in Column M but the code allows for your actual criteria column AH(34) so it should work as required. Please test the code in a copy of your workbook first.
I hope that this helps.
Cheerio,
vcoolio.
Oct 8, 2021 at 09:31 AM
Oct 8, 2021 at 09:35 PM
Cheerio,
vcoolio.