Macro Reference

[Guest]

I have recorded a relative macro where the user rests his cursor on any row
in column A, then clicks a button to insert five rows, name the active cell
r1c1 of that five row range "marker", then go to the range "jobheader", copy
it to the clipboard and then go back to the range "marker", paste the
contents of the clipboard and then delete the range "marker".

When I recorded the macro, it inserted this line of code:

ActiveWorkbook.Names.Add Name:="marker", RefersToR1C1:="=Sheet1!R48&C1"

As you can see, row 48 was "hardcoded" into my macro, so I created two lines
at the beginning of the macro which were:

Dim RowNumber As String
RowNumber = ActiveCell.Row

Those two lines capture my row number without an R reference. Meaning the
cursor is on row 104, those two lines return the value of 104.

I then altered the "hard coded" line above as follows:

ActiveWorkbook.Names.Add Name:="marker",
RefersToR1C1:="=Sheet1!R""RowNumber""C1"

The macro fails because the range named marker is never defined.

I then altered the line of code to:

ActiveWorkbook.Names.Add Name:="marker",
RefersToR1C1:="=Sheet1!R&"RowNumber"&C1"

and that fails as well.

Here is the entire altered macro coding.

Can you please help me with the line to name the range "marker" which will
have to correspond to the cell I was resting on when I pushed this button.

Dim RowNumber As String
RowNumber = ActiveCell.Row
ActiveCell.Rows("1:5").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
ActiveWorkbook.Names.Add Name:="marker",
RefersToR1C1:="=Sheet1!R&""RowNumber""&C1"
Application.Goto Reference:="jobheader"
Selection.Copy
Application.Goto Reference:="marker"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Names("marker").Delete

Thanks so much for all your help.

Tofer

 

[Doug Glancy]

ToferKing,

If you hang around this newsgroup you will see people advise against using
"Select"s and "Activate"s. Unfortunately, recorded code contains lots of
them. Anyways, your code can be greatly simplified if you never change the
Activecell, eliminating the need for the new named range:

ActiveCell.Rows("1:5").EntireRow.Insert Shift:=xlDown
Range("jobheader").Copy Destination:=ActiveCell
Application.CutCopyMode = False

hth,

Doug

 

[Guest]

Doug, Thank you, thank you, thank you.

I'll take note of the Select and Active advice and see if I can incorporate
that idea into my programming world.

I have seen it written in books too, so I am aware of it, just not
experienced enough to know what to do yet.

Tofer