Is there a way to read the active cell location, store it for recall and return to the original cell?

Question

Is there a way to read the active cell location, store it for recall and return to the original cell?

Anonymous

In a macro in excel 2007 or 2010, is there a way to read the active cell location, store it for recall, move to another cell, copy, and finally return to the original cell to complete the paste operation? I am not having much success finding the correct method to accomplish this. I have 75 non-contiguous rows with 52 potential columns in each row where a symbol must be placed. I want to position the cursor manually in the sheet on the correct cell and have the macro complete the insertion of the symbol. I understand how to generate the copy-paste code but am not clear how to accomplish storing a cell reference. Thanks.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

3 additional answers

Answer 1

Anonymous

Dim myC As Range

Set myC = ActiveCell

'Your other code here

myC.Select

But I should note that there is rarely any need to change the selected cell - post your code, and we can modify it to show you how.

HTH, Bernie

0 comments

Answer 2

Thanks Bernie. That did the trick. I was unfamiliar with dim statements for a range. I'll read up on this to improve. Below is my code with most commented out. I appreciate the assist. Mike

Sub ContainSymbol()

'

' ContainSymbol Macro

' This symbol is used to identify the week containment is started.

'

Dim myC As Range

Set myC = ActiveCell

'Your other code here

' Dim location As Variant

Worksheets("Paynter 2011").Activate

MsgBox "Are you sure that you want to enter the Containment symbol in the cell address: " & Application.ActiveCell.Address

' Range(Application.ActiveCell.Address).Select

' Set location = Range(Application.ActiveCell.Address)

' Set myCell = Application.InputBox( _

' prompt:="Please confirm the cell", Type:=8)

' Set MyCell = Range(Application.ActiveCell.Address)

' Set location = Range(Application.ActiveCell.Address)

' MsgBox "Previous Location: " & location

' MsgBox "Previous Location: " & MyCell

Selection.Copy

Range("BP5").Select

ActiveSheet.Paste

Range("C10").Select

Selection.Copy

' MsgBox "Previous Location: " & location

myC.Select

' Range("location").Select

ActiveSheet.Paste

Worksheets("Paynter 2011").Activate

' MsgBox ActiveCell.Value

End Sub

Answer 3

Mike,

It is unclear to me what it is that you want to do. I think you want to copy cell C10 from the sheet "Paynter 2011" and paste it on the activecell. This code would do that. If that is not what you want to do, post back

Sub ContainSymbol2()

Worksheets("Paynter 2011").Range("C10").Copy ActiveCell

End Sub

HTH, Bernie

Answer 4

Anonymous

Thanks Jim, I needed to store the cell reference in a variable so that afterward I could paste a symbol (via macro) in whatever original cell was chosen by the operator after they press a button.

0 comments

Answer 5

Do you need to copy the cell or did you just need the value of the cell. If you only need the value then I would be tempted to use this

Range("B1").value = activecell.value

The above line will put the value from the active cell into B1.

If you need to copy then

range("B1").copy Destination:=Activecell

If this post answers your question, please mark it as the Answer... Jim Thomlinson

Share via

Is there a way to read the active cell location, store it for recall and return to the original cell?

3 additional answers