Moving to a cell from a Pivot Table

[cfatz1]

Hi-

I have a pivot table...and the last cell in the table (bottom right
corner) is the Grand Total. This is a dynamic pivot table that
refreshes on open....so in order to preserve that cell I gave it a
name. The name for the cell is "Total_ABS". I have this code for
"Worksheet_Activate":


Range("Total_ABS").Select
ActiveCell.FormulaR1C1 = "='ADMIN&BUSSERV'!R[-1]C[1]"

The code errors on the second line of code. What I am trying to do is
go to the cell named "Total_ABS" and then make the cell 1 row above and
one column to the right the active cell. When I attempt to do this
using this code I get this error:

"Cannot enter a formula for an item or field name in a PivotTable
Report."

Any ideas on how to get by this?

TIA,
Chris

 

[Debra Dalgleish]

You could find the last cell in the pivot table, and activate the cell
one row up and one column to the right, e.g.:

'======================
Sub ActivateCell()
Dim pt As PivotTable
Dim lRow As Long
Dim iCol As Integer
Set pt = ActiveSheet.PivotTables(1)
lRow = pt.TableRange1.Rows.Count
iCol = pt.TableRange1.Columns.Count
pt.TableRange1.Cells(lRow, iCol).Offset(-1, 1).Activate
End Sub
'====================

Hi-

I have a pivot table...and the last cell in the table (bottom right
corner) is the Grand Total. This is a dynamic pivot table that
refreshes on open....so in order to preserve that cell I gave it a
name. The name for the cell is "Total_ABS". I have this code for
"Worksheet_Activate":


Range("Total_ABS").Select
ActiveCell.FormulaR1C1 = "='ADMIN&BUSSERV'!R[-1]C[1]"

The code errors on the second line of code. What I am trying to do is
go to the cell named "Total_ABS" and then make the cell 1 row above and
one column to the right the active cell. When I attempt to do this
using this code I get this error:

"Cannot enter a formula for an item or field name in a PivotTable
Report."

Any ideas on how to get by this?

TIA,
Chris

 

[cfatz1]

Debra-

Thank you for starting my day off on the right foot! Works perfectly!

-Chris

You could find the last cell in the pivot table, and activate the cell
one row up and one column to the right, e.g.:

'======================
Sub ActivateCell()
Dim pt As PivotTable
Dim lRow As Long
Dim iCol As Integer
Set pt = ActiveSheet.PivotTables(1)
lRow = pt.TableRange1.Rows.Count
iCol = pt.TableRange1.Columns.Count
pt.TableRange1.Cells(lRow, iCol).Offset(-1, 1).Activate
End Sub
'====================

Hi-

I have a pivot table...and the last cell in the table (bottom right
corner) is the Grand Total. This is a dynamic pivot table that
refreshes on open....so in order to preserve that cell I gave it a
name. The name for the cell is "Total_ABS". I have this code for
"Worksheet_Activate":


Range("Total_ABS").Select
ActiveCell.FormulaR1C1 = "='ADMIN&BUSSERV'!R[-1]C[1]"

The code errors on the second line of code. What I am trying to do is
go to the cell named "Total_ABS" and then make the cell 1 row above and
one column to the right the active cell. When I attempt to do this
using this code I get this error:

"Cannot enter a formula for an item or field name in a PivotTable
Report."

Any ideas on how to get by this?

TIA,
Chris

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.

Debra-

Thank you for starting my day off on the right foot! Works perfectly!

-Chris

You could find the last cell in the pivot table, and activate the cell
one row up and one column to the right, e.g.:

'======================
Sub ActivateCell()
Dim pt As PivotTable
Dim lRow As Long
Dim iCol As Integer
Set pt = ActiveSheet.PivotTables(1)
lRow = pt.TableRange1.Rows.Count
iCol = pt.TableRange1.Columns.Count
pt.TableRange1.Cells(lRow, iCol).Offset(-1, 1).Activate
End Sub
'====================

Hi-

I have a pivot table...and the last cell in the table (bottom right
corner) is the Grand Total. This is a dynamic pivot table that
refreshes on open....so in order to preserve that cell I gave it a
name. The name for the cell is "Total_ABS". I have this code for
"Worksheet_Activate":


Range("Total_ABS").Select
ActiveCell.FormulaR1C1 = "='ADMIN&BUSSERV'!R[-1]C[1]"

The code errors on the second line of code. What I am trying to do is
go to the cell named "Total_ABS" and then make the cell 1 row above and
one column to the right the active cell. When I attempt to do this
using this code I get this error:

"Cannot enter a formula for an item or field name in a PivotTable
Report."