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

baheck9200

New Member
Joined
Jan 14, 2009
Messages
7
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Can you give an example or two of what F7 will contain.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,813
Office Version
  1. 365
Platform
  1. Windows
Brent

What does this mean?

baheck9200 said:
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

New Member
Joined
Jan 14, 2009
Messages
7
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

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

baheck9200

New Member
Joined
Jan 14, 2009
Messages
7
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

see the example in the helpfiles
Code:
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:
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
x = Split(Sheets("nameofsheet").Range("F7").Value, "!")
 

baheck9200

New Member
Joined
Jan 14, 2009
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,678
Messages
5,524,241
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top