Moving to a cell from a Pivot Table

  • Thread starter Thread starter cfatz1
  • Start date Start date
C

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
 
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
Click to expand...
 
Debra-

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

-Chris

Debra said:
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
Click to expand...
Click to expand...
 
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

Debra said:
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
'====================

[email protected] said:
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."
Click to expand...
Click to expand...
Click to expand...
 
Back
Top