Copying & pasting data in next available row in same worksheet. [Solved/Closed]

MattW - Oct 27, 2016 at 09:25 PM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen
- Nov 3, 2016 at 02:06 AM
Hello,

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.

Thanks in advance

Matt

See more 

15 replies

vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Oct 28, 2016 at 01:08 AM
0
Thank you
Hello Matt,

Is it always the range A4:E4 that is copied and moved down to the next available row beneath the data set?

Cheerio,
vcoolio.
Hi vcoolio,

No, the range will not always be Row 4, but it will always be Columns A to E.

Thanks
Hi vcoolio

This is excellent - exactly what I was after.

Thank you very much!

Matt
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Oct 30, 2016 at 08:27 PM
0
Thank you
Hello Matt,

Does this mean that each row will then have a drop down list in, say, Column F?

Cheerio,
vcoolio.
That is correct - each row will have a Yes/No drop down box.
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Oct 30, 2016 at 11:29 PM
0
Thank you
Hello Matt,

The following code should do the task for you:-


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:-

https://www.dropbox.com/s/go9pusbz9c206f2/Matt%28paste%20to%20next%20available%20row%20on%20same%20sheet%2C%20worksheet_change%20event%29.xlsm?dl=0

Select "Yes" from the drop downs in any cell in the Criteria column to see the code at work.

I hope that this helps.

Cheerio,
vcoolio.
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Oct 31, 2016 at 01:21 AM
0
Thank you
Hello Matt,

You're welcome. Glad that I was able to help.

If you need the "used" row of data deleted after being moved, just let me know and we'll take it from there.

Cheerio,
vcoolio.
Hi vcoolio,

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?

is that possible?
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Oct 31, 2016 at 02:29 AM
0
Thank you
Hello Matt,

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!

Cheerio,
vcoolio.
Hi vcoolio,

Thanks for this - link to the demo workbook is below

https://www.dropbox.com/s/teev5mqc18n213k/Copy%20Paste%20Macro%20Workbook%20-%20Demo.xlsm?dl=0

Matt
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen > Matt - Oct 31, 2016 at 10:58 PM
Hello Matt,

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:-


https://www.dropbox.com/s/befk07pe8p8rb1n/Matt%282%29.xlsm?dl=0

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.

I hope that we now have it covered for you.

Cheerio,
vcoolio.
Matt > vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Nov 2, 2016 at 12:19 AM
Hi vcoolio,

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?

Thanks
Matt
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Nov 2, 2016 at 06:43 AM
0
Thank you
Hello Matt,

Try the following:-

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:-

https://www.dropbox.com/s/1jg78qr58cbrtbt/Matt%283%29.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.
Excellent - thanks vcoolio!
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen > Matt - Nov 3, 2016 at 02:06 AM
You're welcome Matt. Good luck!

Cheerio,
vcoolio.