VBA and creating a Macro

[tommy]

looking for a Macro for a Command Button
When i click on the Command Button, I want it to automatically
-Insert a New Row (last row)
-Copy an already formatted Row
-and Paste it onto the newly Inserted Row

 

[Gord Dibben]

Why insert a new row beyond the last used row?

Why not just copy the row you want and paste it to the next blank row below
last used row?


Sub findbottom_paste22()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Set rng2 = ActiveCell.EntireRow 'adjust activecell to a range?
rng2.Copy Destination:=rng1
End Sub


Gord Dibben MS Excel MVP

 

[tommy]

I can't just copy and paste since I have locked that area of the worksheet.
It has to be locked in order to keep other users from f.... up my worksheet

the command button is meant to make it easy for them - just click and magic
happens

 

[tommy]

maybe this will help you understand better

rows 1 through 39 are ulocked

rows 40 through end are locked

when i click the command button, i want a new row inserted (in row 40)

copy row 39 and paste onto the new row 40

another thing, what is this findbutton ?

 

[Gord Dibben]

Sub findbottom_paste22()
Dim rng1 As Range
Dim rng2 As Range
With ActiveSheet
.Protect Password:="password", userinterfaceonly:=True
Set rng1 = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'row below last
Set rng2 = .Cells(Rows.Count, 1).End(xlUp).EntireRow 'entire last row
End With
rng2.Copy Destination:=rng1 'copy last row to next row
End Sub

"password" is whatever you have set for a password

There is no "findbutton"

Maybe you mean "findbottom" which is part of the macro name.


Gord