Macro to "go to" specific cell in a different sheet

[baheck9200]

I have a seriew of drop-down combon boxes that ultimately "drill-down" to a resulting sheet & cell reference in cell F7. Based on the criteria selected in these drop-downs the resulting sheet & cell reference (cell F7) will change. I am looking for help with a macro that, when run, will take me to the sheet & cell referenced in cell F7. When I do a goto function it always remember the last place I went to and never goes to the latest data in cell F7.

Brent

 

[VoG]

Can you give an example or two of what F7 will contain.

 

[Norie]

Brent

What does this mean?

When I do a goto function it always remember the last place I went to and never goes to the latest data in cell F7.

 

[baheck9200]

I am on the "layout" tab. From that tab I have 3 drop-down combo boxes that allow me to drill-down to an account.

For example,
Drop-Down 1 = Statement (P&L, Bal. Sht, Cash Flow)
Drop-Down 2 = Category (Sales, Variable Costs, Inventory, etc)
Drop-Down 3 = Account (Gross Sales, Material, Finished Goods, etc)

Drop-downs 2 and 3 adjust as nescessary with the changes selected in drop-downs 1 & 2.

So, after making the selections in the drop-downs cell F7 on the "layout" tab is written to concatenate and vlookup the appropriate sheet and cell number to find the selected statement/category/account per the selected drop downs.

So, in this particular example, cell F7 shows sales'!B161 (Sales sheet and cell B161 on that sheet). I thought I could just record a macro using the cntrl+G function, copy the data in cell F7, paste into the "reference" section of the goto command and it would work. The problem is that the recorded macro takes me to the original sheet/cell spot regardless of the change in my selections from the drop-down. Based on this I am hoping there is a macro that can be used to go to the appropriate sheet&cell based on the choices from the drop-downs since my recorded cntrl+G macro doesn't seem to work. I have tried a couple of things but failed each time.

You probably already gathered this, but I am very new to macros and have trouble fully understanding what they are doing. However I have successfully searched this site, found a macro I was looking for, and applied it to my file. I just can't figure this one out.

Thanks!

 

[VoG]

Try

Sub gto()
Dim x
x = Split(Range("F7").Value, "!")
Application.Goto reference:=Sheets(x(0)).Range(x(1))
End Sub

 

[baheck9200]

Wow, quick respsonse. Thanks.

I tried it exactly as posted and got the following:

Run-Time error '9':

Subscript out of range


This looks like it may be one of those times where my lack of knowledge isn't allowing me to use your code correctly. Should it work exactly as posted, or do I have to adjust some portion(s)?

Thanks, again.

 

[erik.van.geit]

see the example in the helpfiles

Application.Goto Reference:=Worksheets("Sheet1").Range("A154"), _
    scroll:=True

I assume you have in cell F7 something like "Sheet1!A1"
You need to split that: eighter using the "split"function or using something like this:

Sub test()
Dim WhereToGo As String
Dim i As Long
 
WhereToGo = Sheets(1).Range("F7")
i = InStr(1, WhereToGo, "!")
Application.Goto Reference:=Worksheets(Left(WhereToGo, i - 1)).Range(Right(WhereToGo, Len(WhereToGo) - i))
End Sub

 

[VoG]

If you had the sheet containing the sheet/cell reference selected when you ran the code it should work 'as is'.

That error suggests that the sheet name in F7 doesn't exist.

 

[erik.van.geit]

sorry, I see you already got some solution by VOG
If you have an error, then please tell us
1. on what line the error occurs
2. what exactly is in F7

If everything is OK in your workbook, I guess the problem with VOGs code could be that there is no sheetreference and that you are picking the "GOTO" from the wrong sheet?? Then try

x = Split(Sheets("nameofsheet").Range("F7").Value, "!")

 

[baheck9200]

I tried these again and am still stumped. Cell F7 is as follows:

=CONCATENATE("'",F6,"'!",G6,G7)

which results in: 'Sales'!B161

So, based on the selections from the drop-down lists cell F7 tells me where in the workbook to find that account. In this case, I can see that account on the Sales sheet in cell B161.