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

Question

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

Report

MattW - Oct 27, 2016 at 09:25 PM
vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

- 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

Copying & pasting data in next available row in same worksheet.
How to paste data in alternate blank rows in excel - Forum - Excel
Paste data below the last used row - How-To - Excel
Paste data after last row vba ✓ - Forum - Excel
Excel vba create new workbook and paste data ✓ - Forum - Excel
Excel - Re-arrange data from columns into rows - How-To - Excel

6 replies

Ask a question

vcoolio · Answer 1 · 2016-10-28T01:08:30-0400

Answer 1 / 6

vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

214
Oct 28, 2016 at 01:08 AM

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.

Report

Matt
Oct 30, 2016 at 07:17 PM

Hi vcoolio,

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

Thanks

Report

Matt
Oct 31, 2016 at 12:49 AM

Hi vcoolio

This is excellent - exactly what I was after.

Thank you very much!

Matt

vcoolio · Answer 2 · 2016-10-30T20:27:42-0400

Answer 2 / 6

vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

214
Oct 30, 2016 at 08:27 PM

Hello Matt,

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

Cheerio,
vcoolio.

Report

Matt
Oct 30, 2016 at 09:14 PM

That is correct - each row will have a Yes/No drop down box.

vcoolio · Answer 3 · 2016-10-30T23:29:38-0400

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 · Answer 4 · 2016-10-31T01:21:44-0400

Answer 4 / 6

vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

214
Oct 31, 2016 at 01:21 AM

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.

Report

Matt
Oct 31, 2016 at 01:50 AM

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 · Answer 5 · 2016-10-31T02:29:48-0400

Answer 5 / 6

vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

214
Oct 31, 2016 at 02:29 AM

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.

Report

Matt
Oct 31, 2016 at 02:56 AM

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

Report

vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

214 > 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.

Report

Matt > vcoolio

Posts: 1269
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: January 4, 2021

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 · Answer 6 · 2016-11-02T06:43:35-0400

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.

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

6 replies

Your reply

Subscribe To Our Newsletter!