Copying & pasting data in next available row in same worksheet. [Solved/Closed]
MattW -
Oct 27, 2016 at 09:25 PM - Latest reply: vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
- Nov 3, 2016 at 02:06 AM
I would really like assistance to write a vba code for a macro (using a drop down list) that copies data from A4:E4 and pastes it down in the next available row on the same worksheet.
Ideally would like a Yes/No drop down box, where if Yes is selected, the data is copied down to the next available row and if No is selected, no data is copied.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If Target.Value = "Yes" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "E")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code finds any cell in Column F that has a "Yes" value from the drop downs and moves the row of data (A:E) to the next available row at the bottom of the existing data set.
The code is a Worksheet_Change event and needs to be placed in the worksheet module. So, right click on the sheet tab, select "view code" from the menu that appears and then, in the big white field that appears, paste the above code. Go back to the worksheet and test it (remember to test the code in a copy of your work book first). Now, every time that you select "Yes", the relevant row of data will be moved to the bottom of the existing data set.
In the code above, I have just referenced sheet 1 as your work sheet. You may have to change this to suit yourself.
Following is the link to my test work book for you to peruse:-
It would be useful to clean-up the "used" data once it has been copied. Particularly if it was copied to another sheet (so it could still be referenced but out of the way).
Also, is there a way that when the data is copied down (say column C is a date) that the data is copied and 3 months are added to the date?
Yes, its possible. Just so we get it right, upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Use dummy data and place a few notes showing exactly what you would like to do. It will make it easier for us to see how you are using your work book.
Thanks Matt. Have to go, my missus's birthday today. Trying to avoid the dog-house again!
I think that the following revised code should do as you would like:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub
If Target.Value = "No" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
Range("G" & Rows.Count).End(xlUp).Value = Range("G" & Rows.Count).End(xlUp).Offset(, 1).Value + 1
Else: Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
Sheet3.Columns.AutoFit
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I noticed that you have swapped the criteria around with "No" being the criteria used to move data down to the bottom of the data set and "Yes" being used for the clean-up purpose.
Following is the link to my updated test work book based on the sample that you supplied:-
You'll notice that I've made a little change to the sample work book. Instead of having a separate column for the clean-up criteria (Yes), I've added an "Else" statement to the code (lines 14 & 15) which will copy/paste the "used" data to sheet 3 if "Yes" is selected in the drop downs in Column J. Once transferred to sheet 3, the relevant row of data will be deleted from the Tracker sheet. Here, I'm also assuming that you only have "Yes" or "No" in all the drop downs.
The dates will modify as per your request also.
Sheet3 actually shows as Sheet1 in the work book tabs. Sheet3 is the actual sheet code in this instance. This is a more robust method of referring to sheets within a work book.
Is it possible to have 2 separate drop down lists. i.e. Yes/No drop down list to copy & paste the data and then a secondary separate Keep/Remove drop down list to delete the data to sheet 3?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Not Intersect(Target, Range("J:J")) Is Nothing Then
If Target.Value = "No" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
Range("G" & Rows.Count).End(xlUp).Value = Range("G" & Rows.Count).End(xlUp).Offset(, 1).Value + 1
End If
End If
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value = "Remove" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
Sheet3.Columns.AutoFit
End If
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
You now have two criteria columns (J & K). Column K has the "Remove or Keep" drop downs so if you select "Remove", the relevant row of data will be transferred to Sheet3 and will be deleted from the Tracker sheet.
The code works as previous for Column J.
Following is the link to the updated test work book:-
No, the range will not always be Row 4, but it will always be Columns A to E.
Thanks
This is excellent - exactly what I was after.
Thank you very much!
Matt