Macro to move data between sheets on condition
Closed
Carrie
-
Sep 9, 2016 at 08:32 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 12, 2016 at 05:48 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 12, 2016 at 05:48 AM
Related:
- Macro to copy data from one sheet to another based on criteria
- Transfer data from one excel worksheet to another automatically - Guide
- Vba code to copy data from one sheet to another based on criteria ✓ - Excel Forum
- Excel macro to create new sheet based on value in cells - Guide
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
- Transfer data from one excel worksheet to another automatically based on criteria ✓ - Excel Forum
2 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 10, 2016 at 01:32 AM
Sep 10, 2016 at 01:32 AM
Hello Carrie,
See if the following code (untested) helps:-
The code assumes:-
- Sheet1 is the source sheet and sheet2 is the destination sheet.
- Source sheet headings begin in row 5, data in row 6.
- Headings exist in the destination sheet (row 1?).
The code filters sheet1 Column L for the criteria "Yes" and then transfers the relevant row of data to sheet2. It then deletes the "used data" from sheet1 once transferred.
To implement the code:-
- Press Alt + F11 to open the VBA Editor.
- Up the top, select Insert--->Module. A large white code field should then appear.
- Paste the above code into the big white field.
To run the code, you can assign it to a button or press Alt + F8 which will open the macro dialogue box. In the dialogue box, select the code name (in this case TransferData) and click on "Run".
I hope that this helps.
Cheerio,
vcoolio.
See if the following code (untested) helps:-
Sub TransferData()
Dim lr As Long
Application.ScreenUpdating = False
Sheet1.Range("L5", Sheet1.Range("L" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Yes", 7
lr = Sheet1.Range("L" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Sheet1.Range("B6", Sheet1.Range("N" & Sheet1.Rows.Count).End(xlUp)).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheet1.Range("A6", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)).EntireRow.Delete
Sheet2.Columns.AutoFit
End If
Sheet1.[L5].AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code assumes:-
- Sheet1 is the source sheet and sheet2 is the destination sheet.
- Source sheet headings begin in row 5, data in row 6.
- Headings exist in the destination sheet (row 1?).
The code filters sheet1 Column L for the criteria "Yes" and then transfers the relevant row of data to sheet2. It then deletes the "used data" from sheet1 once transferred.
To implement the code:-
- Press Alt + F11 to open the VBA Editor.
- Up the top, select Insert--->Module. A large white code field should then appear.
- Paste the above code into the big white field.
To run the code, you can assign it to a button or press Alt + F8 which will open the macro dialogue box. In the dialogue box, select the code name (in this case TransferData) and click on "Run".
I hope that this helps.
Cheerio,
vcoolio.
Sep 12, 2016 at 04:32 AM
When I try to run this it comes up with a red x and 400? not sure why?
Any ideas?