T
ToferKing
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
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