I'm trying to reference a variable that I have declared in a formula using VBA. I have a variable that I've declared to capture the name of a specific worksheet in Excel. I'm trying to reference that variable in a cell formula, but I don't think I've got the syntax right somehow. I've looked at a lot of forums and other websites, and I thought I'd matched the syntax correctly, but the code is still erroring out. Can someone tell me what I'm doing wrong?
Dim OrigSheet As String
OrigSheet = ActiveSheet.Name
Range("A1").Select
ActiveCell.Formula = "=Row()" & OrigSheet
Dim OrigSheet As String
OrigSheet = ActiveSheet.Name
Range("A1").Select
ActiveCell.Formula = "=Row()" & OrigSheet