Report

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

Ask a question MattW - Last answered on Nov 3, 2016 02:06AM
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 
Helpful
+0
moins plus
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.
Matt- Oct 30, 2016 07:17PM
Hi vcoolio,

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

Thanks
Reply
Matt- Oct 31, 2016 12:49AM
Hi vcoolio

This is excellent - exactly what I was after.

Thank you very much!

Matt
Reply
Add comment
Helpful
+0
moins plus
Hello Matt,

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

Cheerio,
vcoolio.
Matt- Oct 30, 2016 09:14PM
That is correct - each row will have a Yes/No drop down box.
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment
Helpful
+0
moins plus
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.
Matt- Oct 31, 2016 01:50AM
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?
Reply
Add comment
Helpful
+0
moins plus
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.
Matt- Oct 31, 2016 02:56AM
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
Reply
vcoolio 832Posts Thursday July 24, 2014Registration date ModeratorStatus December 8, 2016 Last seen - Oct 31, 2016 10:58PM
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.
Reply
Matt- Nov 2, 2016 12:19AM
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
Reply
Add comment
Helpful
+0
moins plus
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.
Matt- Nov 2, 2016 07:49PM
Excellent - thanks vcoolio!
Reply
vcoolio 832Posts Thursday July 24, 2014Registration date ModeratorStatus December 8, 2016 Last seen - Nov 3, 2016 02:06AM
You're welcome Matt. Good luck!

Cheerio,
vcoolio.
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!