Excel VBA: Formula with reference to cell in another worksheet (variable)

Question

I am trying to create a macro that links a cell from another worksheet (this is stored as a variable called SheetName). The user is prompted with an Input box to select a cell. I would like to have a cell in another worksheet reference to the selected cell.

Here is the relevant code:

Dim WorkRng As Range
    Total1 = "Select Total cell"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", Total1, WorkRng.Address, Type:=8)
    Worksheets("WorksheetA").Range("C6").formula = "=" & 'SheetName.Name' & "!" & WorkRng.Address

The last line is where I am running into object errors. Any help is greatly appreciated!

Community · Accepted Answer · 2019-05-06 00:58:26Z

3

Try,

Worksheets("WorksheetA").Range("C6").formula = _
   "='" & SheetName.Name & "'!" & WorkRng.Address

'note the quote here "='" and here "'!"

edited May 6, 2019 at 0:58

CommunityBot

11 silver badge

answered Aug 2, 2018 at 13:49

user4039065

Sign up to request clarification or add additional context in comments.

Comments

QHarr · Accepted Answer · 2018-08-02 14:28:21Z

1

Where is sheetname defined? It then needs to be concatenated into the string

DIm SheetName As Worksheet
Set SheetName = Worksheets("Sheet2")

formula then

"=" & SheetName.Name  & "!" & [A1:B2].Address

edited Aug 2, 2018 at 14:28

answered Aug 2, 2018 at 13:51

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

2 Comments

user4039065 Over a year ago

External includes the worksheet name; in fact it also includes the workbook name and path which Excel evaporates away if unnecessary. You might be duplicating the worksheet name.

QHarr Over a year ago

Aghhhh... thanks for letting me know. Was questioning myself. Should have just done some quick homework.

Collectives™ on Stack Overflow

Excel VBA: Formula with reference to cell in another worksheet (variable)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related