Macro to always take you to the last active row

[Rodney]

Hi guys,

Looking for some help...

I need to create a Macro which will always take you to the last active row
in a spreadsheet and then insert three new lines which will be formatted in
the same manner as the line above. I'm creating a risks and issues log so the
three lines are merged together for half of the columns and then not for the
other half if this causes problems with the Macro.

Is this possible? I've tried to do it by recording a macro but doesn't seem
to work as when I insert the new lines they always appear on the same
row....annoying.

Hopefully you can help.

Cheers,
Rod

 

[muddan madhu]

Sub test()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count
Rows(r).Copy
Rows(r + 1 & ":" & r + 3).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

 

[Dave Peterson]

If I know my data, I like to pick out a column that always has an entry when the
row is used.

In this code, I used column A:

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim LastRow As Long

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(LastRow).Copy
.Rows(LastRow).Offset(1, 0).Resize(3).PasteSpecial Paste:=xlPasteFormats
End With

Application.CutCopyMode = False
End Sub

 

[Rodney]

Hi Muddan,

Three new rows have been inserted but unfortunately from colum A-Q the three
new rows should have merged as is the case with the row above but they
haven't?

Any suggestions?

Many thanks,
Keith