How to return to the original active cell?

[Saunick]

Say I have cell B5 selected. I'm creating VBA code that copies data from a cell above B5 and then also copies data from a cell to the right of B5. The issue I'm having is telling the code to go back to B5 after I've copied the cell above it so that I can then copy the cell to the right.

I'm not sure if I'm using the wrong syntax or if I'm setting up my variables incorrectly, but the last line of code below is what gives me an error, and I can't figure out how to fix it:


Sub PQ_Resolver()
'
' PQ_Resolver Macro
'
' Keyboard Shortcut: Ctrl+e
'
Dim R As Integer
Dim C As Integer

R = ActiveCell.Row
C = ActiveCell.Column

ActiveCell.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.Copy
Range("W1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells(R & C).Select


My thought is to take the row and column of the active cell (B5) and then use those to reselect that cell later on before moving on in the code, but as stated above it's not working.

[p45cal]

How to return to the original active cell?The best way is not to have to! Don't change what cell is the active cell and what's selected (they aren't always the same thing).
This does the same thing:
Sub PQ_Resolver()
'
' PQ_Resolver Macro
'
' Keyboard Shortcut: Ctrl+e
'
Range("W1").Value = ActiveCell.End(xlUp).Offset(1).Value

[SamT]

p45cal is correct.

Also don't use Integer Types unless you are working on an olde DOS machine with very little RAM. Integers will fail on numbers > 32K.

In your code, Cells(R&C) = Range("AZ1"), if it works at all: You need Cells(R, C)

On the very rare occasions I must use "ActiveCell" The very first thing do is set a Variable to that Range.. I would refactor your code like

Dim myRange as Range

Sub BlahBlah()
Set myRange = Activecell

Range("W1") = myRange.End(xlUp).Offset(1, 0).Value
End Sub

Sub BlehBleh()
With myRange ...
More code here