Macro Copy & Paste?

redart

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows
I've got the Macro blues. I'm trying to get VBA to insert a new line at the foot of a Worksheet range, then copy the contents of the last line into the newly created blank line. Problem is trying to make this repeatable, as when I run the macro a second time, all the cell references point to the line number which was active when I recorded the macro, and it simply overwrites the previous line. Can anyone suggest a way to increment the line numbers used each time a macro is run?
Thanks. Tony
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Something like this:

Code:
Sub Test()
    Dim Rng As Range
    Set Rng = Range("A65536").End(xlUp).EntireRow
    Rng.Copy Rng.Offset(1, 0)
End Sub
[CODE]

It finds the last used cell in column A and expands to cover all columns. Then it copies the contents one row down.
 

redart

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows
Thanks Andrew. I'm a newcomer to BB's (I think yesterday was the first time I've posted), but I'll definitely be using them more often from now. Your reply got me on the right track. I needed to insert a blank line before copying the last line down (due to other working cells below), and then I needed to clear some of the cells in the new line, as well as a couple in the previous line. The final code looks like this:-

Sub Test()
Dim Rng As Range
Set Rng = Range("A65536").End(xlUp).EntireRow
Rng.Offset(1, 0).Activate
ActiveCell.EntireRow.Insert
Rng.Copy Rng.Offset(1, 0)
Rng.Cells(2, 17).ClearContents
Rng.Cells(2, 25).ClearContents
Rng.Range(Cells(2, 28), Cells(2, 32)).ClearContents
Rng.Range(Cells(2, 46), Cells(2, 47)).ClearContents
Rng.Range(Cells(2, 51), Cells(2, 52)).ClearContents
Rng.Range(Cells(1, 33), Cells(1, 36)).ClearContents
End Sub

Probably not as concise as possible, but I'm new to VBA also, having previously just use record, so happy to get my first real code running, and working properly. Thanks again.
 

Forum statistics

Threads
1,144,148
Messages
5,722,773
Members
422,456
Latest member
lipa

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