Related:
- Excel copy row to another sheet based on cell value
- Excel copy row to another sheet based on cell value formula - Best answers
- Excel move row to another sheet based on cell value - Best answers
- Excel move row to another sheet based on cell value ✓ - Forum - Excel
- Excel copy row to another sheet based on cell color ✓ - Forum - Excel
- Copy Rows from one sheet to others based on a condition ✓ - Forum - Office Software
- Excel copy row to another sheet based on cell value formula ✓ - Forum - Excel
- Excel move row to another sheet based on cell value without macro - Forum - Excel
6 replies
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
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.
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Sorry LeedMe, try this link instead:-
https://www.dropbox.com/s/h8v8w6b6zaofr3f/LeedMe%28Master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Cheerio,
vcoolio.
https://www.dropbox.com/s/h8v8w6b6zaofr3f/LeedMe%28Master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Cheerio,
vcoolio.
btcoutermash
That Excel sheet works great!
The issue I have now is that when I try to use that macro on my existing document I get an error. Also can it not delete the the data on Sheet1? I would still need that sheet for multitude of reasons.
This is a slimmed down copy of my document.
I appreciate the help!!
https://www.dropbox.com/s/fpns1c7fy6w8myc/BACKLOG_TEST.xlsm?dl=0
The issue I have now is that when I try to use that macro on my existing document I get an error. Also can it not delete the the data on Sheet1? I would still need that sheet for multitude of reasons.
This is a slimmed down copy of my document.
I appreciate the help!!
https://www.dropbox.com/s/fpns1c7fy6w8myc/BACKLOG_TEST.xlsm?dl=0
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Hello btcoutermash,
I assume that you are the same Poster (LeedMe).
Following is the code adjusted to suit your workbook:-
One of the reasons that you may have received an error is because you placed a full stop after "MACH" in the array. The code would not have recognised this as a criteria as the input sheet has "MACH" not "MACH." For the same reason, the code would not have recognised a sheet named "MACH." In your case, sheet names and criteria must be spelt exactly the same.
Another reason would be that the sheet code for your Sheet1(your input sheet) is actually Sheet3. You'll notice that in the adjusted code above I have altered this for you.
Another reason would be that your data begins in row 8 with headings in row 7. The sample I supplied has the data begining in row 2 with headings in row 1. I have adjusted this also for you.
I have also removed the delete line of code.
Test the adjusted code in a copy of your work book first.
I hope that this helps.
cheerio,
vcoolio.
I assume that you are the same Poster (LeedMe).
Following is the code adjusted to suit your workbook:-
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)
Sheet3.Range("G7", Sheet3.Range("G" & Sheet3.Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 7, , 0
lr = Sheet3.Range("G" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Sheet3.Range("G8", Sheet3.Range("G" & Sheet3.Rows.Count).End(xlUp)).EntireRow.Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2)
Sheets(ar(i)).Columns.AutoFit
End If
Next i
Sheet3.[G7].AutoFilter
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"
End Sub
One of the reasons that you may have received an error is because you placed a full stop after "MACH" in the array. The code would not have recognised this as a criteria as the input sheet has "MACH" not "MACH." For the same reason, the code would not have recognised a sheet named "MACH." In your case, sheet names and criteria must be spelt exactly the same.
Another reason would be that the sheet code for your Sheet1(your input sheet) is actually Sheet3. You'll notice that in the adjusted code above I have altered this for you.
Another reason would be that your data begins in row 8 with headings in row 7. The sample I supplied has the data begining in row 2 with headings in row 1. I have adjusted this also for you.
I have also removed the delete line of code.
Test the adjusted code in a copy of your work book first.
I hope that this helps.
cheerio,
vcoolio.
