Goto Cell referenced in another cell

[brianfosterblack]

On my 1Master Worksheet I want to go to the worksheet and cell referenced in Cell 1Master!Z6
I have used a formula to create a reference Muller!R1C1 (also tried a formula to create a reference Muller!A1) - Reference is based on the Worksheet name selected
I then used this code in my macro to got to this reference

Application.Goto Reference:=Range("1Master!Z6").Value

This is not working although it does work in other older workbooks I have.
I am doing this as I have a form which shows me all the worksheets in my workbook. I select the worksheet from the form which then puts the formula into Z6 so I can go to cell A1 of the selected Worksheet.
Can anyone please help me? I am probably doing this all wrong

 

[Fluff]

Try it like

Application.Goto Reference:=Range(Range("1Master!Z6").Value)

 

[brianfosterblack]

Try it like

Application.Goto Reference:=Range(Range("1Master!Z6").Value)

Hi fluff,
This is not working. I get the following error

Runtime error 1004
Method 'Range' of object'_Global'failed

I currently have the result of my formula in Z6 showing as MyGas!R1C1
I have also tried with the formula result as MyGas!A1
I copied your code and pasted it exactlyas is into my macro
It hits error on the line of code you provided above.

 

[Fluff]

Where is the code located?

 

[brianfosterblack]

Where is the code located?

In a VBA Module

Sub GotoWorksheet()
    Sheets("1Master").Select
    Range("1Master!Z4").ClearContents
    FrmTab.Show
    If Range("1Master!Z4").Value = 1 Then
    Exit Sub
    End If
    If Range("1Master!Z4").Value = "" Then
    MsgBox ("You did not select a Worksheet, Try again")
    Exit Sub
    End If
    Application.Goto Reference:=Range(Range("1Master!Z6").Value)
End Sub
Should the Address in Z6 read as 1Master!Z6 or as 1Master!R1C1

 

[Fluff]

In a VBA Module

Do you mean a standard module? or is it in a sheet module?

The address in 1Master!Z6 should use A1 notation, unless you are using R1C1 notation in the sheet.

 

[brianfosterblack]

Do you mean a standard module? or is it in a sheet module?

The address in 1Master!Z6 should use A1 notation, unless you are using R1C1 notation in the sheet.

Standard Module

 

[Fluff]

In that case check that the sheet name is a valid sheet name.
Also if the sheetname contains spaces you need to include the relevant apostrophes in the name.

 

[brianfosterblack]

The Sheetname

In that case check that the sheet name is a valid sheet name.
Also if the sheetname contains spaces you need to include the relevant apostrophes in the name.

Iis a valid sheetname. I am using it in other VBA macros and it works fine
There are no spaces in the sheetname or at the end of the sheetname

In that case check that the sheet name is a valid sheet name.
Also if the sheetname contains spaces you need to include the relevant apostrophes in the name.

Hi Fluff, I slept on it and came back to it this morning to realize the problem was that my worksheet reference in in cell Z5 not Z6.
Sorry for wasting your time. Thank you for your patience and help.

 

[Fluff]

Glad you sorted it & thanks for the feedback.