Anchoring a cell and sheet in Excel

[Roosters]

I need to anchor a sheet via a macro without refering to the specific sheet
ie - Sheets("Sheet 1&quot.Select
What I need is something like
Dim anchor_sheet
anchor_sheet = ActiveCell.Address
Range(anchor_sheet).Select
Selection.copy
Sheets("HANDOVER&quot.Select
Selection.Paste
Range(anchor_sheet).Select
This anchors the cell but not the sheet ie when I refer back to the anchored sheet it goes to the same cell but in the active sheet

 

[kathryn]

You need to set a variable to save your current sheet.

Something like this


Dim strAnchorCell as string
Dim wksAnchor as Worksheet
strAnchorCell = ActiveCell.Address
set wksAnchor = ActiveSheet
Range(strAnchorCell).Select
Selection.copy
Sheets("HANDOVER&quot.Select
Selection.Paste
Sheets(wksAnchor.Name).Range(strAnchorCell).Select


Using this syntax, I'm not sure why you need the AnchorCell. How does your code know where on HANDOVER to paste?