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
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Ok, we know the lastrow that contains data. If you want to hardcode the number of columns, then replace that code with something like b=5 for 5 columns

The code you pointed out above goes to the extreme right of the worksheet and then simulates pressing ctrl+left arrow to get to the last used column in row 1 which i assume is same all through

Are you running excel 2003? if you are XFD wont work for you, Replace XFD with the last column in an excel 2003 workbook which should be "IV" or so i think
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
How come you have not used any variables in your code

How do you mean? 'cos lastrow, i and b are all variables or did you mean declaring the variables
 

Watch MrExcel Video

Forum statistics

Threads
1,127,549
Messages
5,625,443
Members
416,106
Latest member
Geo0

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