How can I use the contents of a cell as a variable which must be a string

Question

I am using the code below to refresh a HFM worksheet and update two of the variables in the POV, Year and Period.

I need to replace the 'Mar' in "Period#Mar" with the contents of a cell on a separate worksheet (which is currently 'Mar', but will change each month).

I have tried to create a range object, but the HypSetPOV function requires a string, rather than a range. So I am not sure if I somehow need to convert my range into a string, or do something different.

The code without the range (which updates the period to Mar):

Sub HFM_Refresh()

Dim SheetName As String
Dim sts As Long
SheetName = "1 - PII PL Reporting Month"

ActiveWorkbook.Worksheets(SheetName).Visible = True
ActiveWorkbook.Worksheets(SheetName).Activate
ActiveWorkbook.Worksheets(SheetName).Range("A1").Activate

X = HypSetPOV(SheetName, "Year#2019", "Period#Mar")

sts = HypMenuVRefresh()

If sts <> 0 Then
MsgBox "Error - refresh not complete on worksheet " & SheetName
End If

End Sub

The code I tried with a range (which does not update the period at all):

Sub HFM_Refresh()

Dim SheetName As String
Dim sts As Long
Dim MonthMember As Range
SheetName = "1 - PII PL Reporting Month"
Set MonthMember = ActiveWorkbook.Worksheets("Update").Range("D9")

ActiveWorkbook.Worksheets(SheetName).Visible = True
ActiveWorkbook.Worksheets(SheetName).Activate
ActiveWorkbook.Worksheets(SheetName).Range("A1").Activate

X = HypSetPOV(SheetName, "Year#2019", "Period#MonthMember")

sts = HypMenuVRefresh()

If sts <> 0 Then
MsgBox "Error - refresh not complete on worksheet " & SheetName
End If

End Sub

Thanks in advance :)

Answer 1

You can do this:

X = HypSetPOV(SheetName, "Year#2019", _
              "Period#" & ActiveWorkbook.Sheets("Udatate").Range("D9").Value)