I have a somewhat large file that I'm trying to dynamically subtotal
with a pivot table and retreive data via vba and put it in another
workbook (2 open at once). There are two questions I have: 1) Is the
method I'm using the most efficient or is there a better way?, and 2)
If my method is okay, then what am I doing wrong?

Okay, the details... The header has depts (potentially from 1-100 in
the columns), season-type (1-50), metric type (sales, inventory,
receipts, etc) then 26 weeks going across the top. I need to
dynamically sum differing combinations of departments by season-type
for a given metric by week. (Not all possible depts or season types
will always exist.) I've created a pivot table with vba and then can
easily update the pivot table with the combinations that I want to see,
but my problem becomes when I'm trying to use the .getdata command. If
a given combination doesn't exist then run-time error 1004 pops up.
I've put in an error trap and it catches some but not all (it gets
through about 7 of them????!!!!).

'dept is in the page field of the pivot table summing depts 1-25
lkup = "7/23/2005"
ssn = 11 (a variable, but 11 here for example)
metric = "Sales" (again, another variable)
div_ssn = "'Sum of " & lkup & "' " & ssn & " " & metric
Workbooks(datawb).Activate
On Error GoTo chk_error
act_sls_data = ActiveSheet.PivotTables("data_pivot") _
.GetData(div_ssn)
chk_error:
Select Case Err.Number
Case Is = 1004
act_data = 0
End Select

ThisWorkbook.Activate
range("sales1").Cells(ssn_cntr, curr_col).Offset(0, y) =
act_sls_data


Any ideas? How about Consolidate or Group or Subtotal with this amount
of data? Needs to be fast and dynamic.

Thanks!
Pete