Refresh tabs

[MACRE0]

Excel is having a tough time shifting from one tab to another and
recalculating the page. If however I clikc F9 the tab is fine. Is
there anyway to make it when clicking upon the tabs that they refresh
automatically? I was considering a macro that would refresh every 15
seconds or something, but I thought I'd ask this first. I'd
appreaciate help with either, thanks.

 

[Nick Hodge]

MACRE0

Not sure why it won't calculate but you could force it by placing code in
the Workbook_SheetActivate event like so

Private Sub Workbook_SheetActivate(ByVal sh As Object)
sh.Calculate
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[MACRE0]

While that's exactly the type of thing that I'm looking for, it doens't
seem to work in this case. If you'd be willing, I'd happily send you
the speadsheet so you can see exactly what I mean about excel not
caculating the tab until some other action is performed.

 

[Nick Hodge]

MACRE0

Try

Application.CalculateFull

In place of sh.calculate

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[MACRE0]

No :~( but I've discovered the root of the problem. I am basing
several lookup formulas on a truncated down version of the
cell("filename") to get the tab name. Is there better way of
referencing the tab with a formula than this?
{=MID(CELL(“filename”),FIND(“]“,CELL(“filename”))+255)}
Though the tabs of course remain static, the cell in the sheet states
the previous tab's name until recalced. Perhaps if I change this
formula, which all the lookups are based upon, a sheet refresh will no
longer be required.

 

[Gord Dibben]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Any cell address like A1 will anchor the formula to the worksheet.


Gord Dibben Excel MVP

 

[Piranha]

Hi,
I may not understand what you are doing but this will give you the tab
name
in whatever cell you put the formula in.

Why do you have array brackets on your formula?

Try this:
'=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Don't change the A1 except:
To refer to another sheet, replace A1 in formula with SheetName!A1.

No :~( but I've discovered the root of the problem. I am basing
several lookup formulas on a truncated down version of the
cell("filename") to get the tab name. Is there better way of
referencing the tab with a formula than this?
{=MID(CELL(“filename”),FIND(“]“,CELL(“filename”))+255)}
Though the tabs of course remain static, the cell in the sheet states
the previous tab's name until recalced. Perhaps if I change this
formula, which all the lookups are based upon, a sheet refresh will no
longer be required.

 

[MACRE0]

TY, that lack of an anchor really did me in. Shame on me for trying to
cut corners.
No longer does my active sheet retain the prior tab's just viewed
information.