CODEWITHSUNDEEP
vbavariablesreferencecell
John Grayson
John Grayson

Reputation: 1496

How to pass worksheet name as variable in cell

I am trying to write a macro to set the value of a cell that refers to another worksheet as a variable:

Dim worksheetName as String

worksheetName = "Pricing Sheet"
Worksheets("Main Sheet").Cells(11, 4).Value = "='worksheetName'!" & "A1"

HOWEVER, the result is the cell ends up equaling "='worksheetName'!" & "A1" WHEREAS the end result I want is the cell equaling Worksheets("Main Sheet").Cells(11, 4).Value = "='Pricing Sheet'!" & "A1"

The variable worksheetName could take on multiple values, not just "Pricing Sheet" therefore I need it to be a variable in the cell.

Please help! Thanks!

Upvotes: 1

Views: 2010

Answers (1)

PatricK
PatricK

Reputation: 6433

Use Formula, not Value. I guess you should use somthing like this for dynamic variable.

worksheetName = Worksheets("Main Sheet").Cells(R,C).Value
Worksheets("Main Sheet").Cells(11, 4).Formula = "='" & worksheetName & "'!" & "A1"

Upvotes: 1

Related Questions