VBA- Copy data from last row to next row

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
Hi,

I am after a code that will copy data from last row to next row and then clear data from the copied row between Col A-K.

So in the below test data, copy row 2 to row 3 then clear data from A3 to K3.

Please note Col A will have data validation list, so the code will need to paste validation aswell


Excel Workbook
ABCDEFGHIJKLM
1Header1Header2Header3Header4Header5Header6Header7Header8Header9Header10Header11Header12Header13
2Test1Test2Test3Test4Test5Test6Test7Test8Test9022Test13
Sheet1
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Although am still confused as to your request, 'cos column A has data validation and you paste it in the next row and still delete column (A-K), so of what use was pasting the validation in the first place?

This is an idea, make sure the cursor is in the active row.

Code:
Sub Macro16()
    a = ActiveCell.Row
    b = Range("XFD" & a).End(xlToLeft).Column
    
    Range(Cells(a, 1), Cells(a, b)).Copy Range("A" & a + 1)
    Range(Cells(a + 1, "A"), Cells(a + 1, "K")).Delete
End Sub
 
Upvote 0
I did not say delete i said clear contents. The objective is to copy the formula and format from the above row so i can input new data.
 
Upvote 0
Having the cursor in Active cell is not good, i require code to copy data regadless where the cursor is. I have changed "Delete" to Clear Contents, it works but need to remove the active cell part
 
Upvote 0
In this form, it moves from row to row performing the same operation

Code:
Sub Macro16()
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    b = Range("XFD1").End(xlToLeft).Column
    
    For I = 1 To lastrow
        Range(Cells(I, 1), Cells(I, b)).Copy Range("A" & I + 1)
        Range(Cells(I + 1, "A"), Cells(I + 1, "K")).ClearContents
    Next I
End Sub
 
Upvote 0
I also do not understand why XFD1 was used? when i run the code i get

b = Range("XFD1").End(xlToLeft).Column

when i run the code i get run time error 1004 "Method Range of Object Global failed
 
Upvote 0
Which version of Excel are you using?

If it's one of the newer version then convert the range to a Table.Excel tables will copy formulas and formatting to any row added to the Table


Read: Overview of tables

In all versions of excel you can set Excel to copy formulas and formatting from Excel Options.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top