Copy rows to other sheets based on value in column
Solved/Closed
LeedMe
Posts
5
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 12, 2018
-
Updated on Mar 28, 2017 at 07:47 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 30, 2017 at 08:59 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 30, 2017 at 08:59 AM
Related:
- Excel move rows to another sheet based on criteria
- Vba code to move rows to another sheet based on criteria - Best answers
- Excel move row to another sheet based on cell value automatically - Best answers
- How to screenshot excel sheet - Guide
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
- Mark sheet in excel - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Vba code to move rows to another sheet based on criteria ✓ - Excel Forum
6 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Mar 29, 2017 at 07:51 PM
Mar 29, 2017 at 07:51 PM
Hello LeedMe,
Try the following code in a copy of your work book first:-
The code filters Column G for the criteria "MACH" and "FAB" and then, when found, transfers the relevant row of data to the relevant sheet. The "used" data in the main sheet is then deleted. You'll note that in the code above, I have used the sheet code rather than the sheet name.
Following is the link to a sample that I have prepared for you. Download the sample and then click on the "RUN" button to see it work.
https://www.dropbox.com/s/h8v8w6b6zaofr3f/LeedMe%28Master%20sht%20to%20multi%20shts%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.
Try the following code in a copy of your work book first:-
Sub TransferData() Dim ar As Variant Dim i As Integer Dim lr As Long ar = Array("MACH", "FAB") Application.ScreenUpdating = False Application.DisplayAlerts = False For i = 0 To UBound(ar) Sheet1.Range("G1", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 7, , 0 lr = Sheet1.Range("G" & Rows.Count).End(xlUp).Row If lr > 1 Then Sheet1.Range("A2", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2) Sheet1.Range("A2", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).Delete Sheets(ar(i)).Columns.AutoFit End If Next i [G1].AutoFilter Application.DisplayAlerts = True Application.CutCopyMode = False Application.ScreenUpdating = True MsgBox "Data transfer completed!", vbExclamation, "Status" End Sub
The code filters Column G for the criteria "MACH" and "FAB" and then, when found, transfers the relevant row of data to the relevant sheet. The "used" data in the main sheet is then deleted. You'll note that in the code above, I have used the sheet code rather than the sheet name.
Following is the link to a sample that I have prepared for you. Download the sample and then click on the "RUN" button to see it work.
https://www.dropbox.com/s/h8v8w6b6zaofr3f/LeedMe%28Master%20sht%20to%20multi%20shts%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.