Yet another copy/paste macro question

B

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!
 
S

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...
 
D

Don Guillett

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

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?
 
B

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.
 
S

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top