Yet another copy/paste macro question

[Btate0121]

My apologies in advance if this is redundant. Didn't find what I was looking
for via search.

I have a client info spreadsheet that i'm looking to automate a bit more. I
put in a button and tied it to a macro that copies the line the cursor is on,
inserts a row below where the cursor is, and erases all client data (save
for the cells with formulas in them) to create a blank row with formulas for
a new client. This is tedious and doesn't work too well when creating client
data in a new section of the spreadsheet (where this is no source row to copy
from). Here's what I have:

ows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -15).Range("A1").Select
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8

What I would like.. is to eliminate all those rose of "select, clear
contents" and just have it copy the row from "1:1:" where I have a blank
client template.


so.. it would work like this, the cursor is set to the row where the new
line will be put in (the line will be added to the row BELOW where the cursor
is set), it copies row 1:1, and pastes it into the newly created blank row.

Thanks in advance!

 

[Sheeloo]

Try this
Sub copy()
Rows("1:1").EntireRow.copy _
Destination:=ActiveCell
End Sub

Active cell has to be in Col A for this to work...

 

[Don Guillett]

Try this
Sub insertrow()
myrow = ActiveCell.Row + 1
Rows(myrow).Insert
Rows(1).Copy Rows(myrow)
End Sub

 

[Btate0121]

This seems to do the job... except it keeps hiding the added row.

Also, I forgot to mention the worksheet is protected. So I'll need the
"protect. Password="" " function. Whe I added it manually, it seems to break
the code. any help there?

 

[Btate0121]

figured out the hidden row. I need to hide row 1... which is why the copied
row hides (because the source row is hidden). So I just need to know how to
unlock it, copy/paste the hidden row, and finally re-lock the sheet.

 

[Sheeloo]

Try
Sub copy()
ActiveSheet.Unprotect Password:="abc123"
Rows("1:1").Hidden = False

rng = "A" & Right(ActiveCell.Address, 2)
Range(rng).Select
Rows("1:1").EntireRow.copy _
Destination:=ActiveCell

Rows("1:1").Hidden = True
ActiveSheet.Protect Password:="abc123"
End Sub

 

[Don Guillett]

Or modify mine as:

Sub insertrow()
myrow = ActiveCell.Row + 1
Rows(myrow).Insert
With Rows(1)
.Hidden = False
.Copy Rows(myrow)
.Hidden = True
End With
End Sub