Solved: Update a COUNTA cell in a pivot table

[Gingertrees]

(Orig. posted at Mr. Excel, though I've added since:
http://www.mrexcel.com/forum/showthread.php?t=436935 )

What I want: a COUNTA function on my pivottable (highlighted where I want it) so I can track how many clients overall (16), and how many per employee (John has __ clients, Sally has __, etc). I think I can do this with a WorkSheet_Change sub...but I do not have it perfected.

what do I need to change to fix it so it will do the above? Here's what I attempted, and I've attached some sample data/table
[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Abba As Long
If Target.Address = "$B$1" Then
Application.EnableEvents = False
Abba = Application.WorksheetFunction.CountA(Range("A1:A1000"))
'counts clients served; the minus 4 is so it doesn't count the
'header and total cells
With Range("C2")
Target("C2") = Abba - 4
End With
End If

End Sub[/vba]

[Bob Phillips]

Isn't it just

=COUNTA(A:A)-4

[Gingertrees]

How do I put that in the VBA code? The pivot table is the result of a macro that can be run for various things.

[Bob Phillips]

[vba]

Range("C2").Formula = "=COUNTA($A:$A)-4"
[/vba]

Where is your picture from?

[Gingertrees]

I tried this, to no avail:[VBA]
Option Explicit
Private Sub Worksheet_change(ByVal target As Range)
If target.Address = "$B$1" Then
Application.EnableEvents = False
With Range("C2")

.Formula = "=COUNTA($A:$A)-4"
target = Range("C2")
End With
End If

End Sub

[/VBA]
If you check my original link, I have posted there my entire code. This is part of a way for me to change lengthy government reports into usable data. The code I have constructed allows me to choose a text file for the basis of my spreadsheet, save it in a directory of my choice (as .xls file), clean up any useless crap, and throw the cleaned data into a pivot table. This way I can see the number of clients per employee, quantity of services delivered by employee, etc. Therefore, if I can compare the number of clients an employee has vs. the other employees vs. the entire branch, I have usable data.

Sidenote: my avatar is a fjord in Norway, though it reminds me of American fjords I have lived near.

[Bob Phillips]

Sorry, I didn't really look at your original code.

This works

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Abba As Long

If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
Application.EnableEvents = False
Abba = Application.WorksheetFunction.CountA(Range("A1:A1000"))
Me.Range("C2").Value2 = Abba - 4
Application.EnableEvents = True
End If

End Sub
[/vba]

[Gingertrees]

OK, that made it work in my test file...and brings me to a need for new thread. Thanks! Happy Holidays

[Gingertrees]

So my spreadsheet pivotQUEST has a macro that will open a text file of your choice, convert it, save a copy as a new sheet in pivotQUEST, then throw that data into a pivot table in yet another sheet.

How do I get that Worksheet_Change routine into my new pivot sheet, since I don't know what the name will be and it could be repeated over and over again?

[Bob Phillips]

Create a wokbook_SheetChange event and tets for some attribute that only a pivot sheet will have.

[Gingertrees]

I'm really not skilled with that. I looked up some stuff and tried this, but it does nothing:[VBA]
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
'ThisWorkbook module
Dim Abba As Long
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
If Not Intersect(Target, Range("B1")) Is Nothing Then
Abba = Application.WorksheetFunction.CountA(Range("A1:A1000"))
With Range("E4")
.FormulaR1C1 = Abba - 7
'also tried .Value = Abba-7, no luck there either
'//////errors at the Range("E4") point above. I don't know why!/////
End With
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub[/VBA]

Please help! I know nothing of Workbook_sheetChange :-(