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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543

ADVERTISEMENT

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.
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
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
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
How come you have not used any variables in your code
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
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
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,997
Messages
5,622,082
Members
415,875
Latest member
Tarali

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
Top