Easy Excel VB question (I think!)

[Just Kev]

Hi, I was wondering if someone could just help me out with (what I
think is) an easy query on VB syntax in Excel.

I have the following statement...

Sheets("rm tab4").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "",
xlDataAndLabel, True
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
End With

This works just fine, except it needs to visit the 'rm tab4' page in
order to execute. This is a pain because when I run the macro from a
page other than 'rm tab4' (which I need to be able to do), it
transports me to rm tab4 and then leaves me there.

To run this without having to visit the page I think I need to lose the
..Select statement. My attempt at this has been...

Sheets("rm tab4").PivotTables("PivotTable1").PivotSelect "",
xlDataAndLabel, True
Sheets("rm
tab4").PivotTables("PivotTable1").Borders(xlInsideHorizontal).LineStyle
= xlContinuous

....but Excel just laughs at me. Can anyone help me please?

Cheers.

Kevin

 

[johnhildreth]

Someone familiar with pivot tables can likely help with the syntax.
But a fairly simple solution is to
1) Check from which sheet you run this - dimension a worksheet variable
(StartSheet) and assign it the Activesheet
2) Run this - just what you do now
3) Re-activate the start sheet - StartSheet.activate

HTH,
John

 

[Just Kev]

HI, thanks John. Unfortionately I call the macro from a number of
different places and so cant just specify one single place to return
to. Nice shortcut though!!