I am running a macro that will capture data on pivot tables and enter it onto
a template. Some months, for example "Europe" will not come up on the pivot
table because there was no activity to report, but I still need a zero in
place of a #REF when there is nothing to capture.

When I include the On Error Go To 0, nothing happens, It will still come up
with #REF. Can anyone help with this?

Workbooks.Open ( _
"C:\Automated\Pivots.xls")
Windows("Master.xls").Activate
' Europe
-----On Error GoTo 0 <<<<------
Sheets("Europe").Select
ActiveCell.Offset(0, 0).Range("A1:B1").Select
ActiveCell.FormulaR1C1 = _

"=GETPIVOTDATA(""ID"",'[Pivots.xls]Mthly_Ts'!R1C1,""Breakdown"",""Europe"",""Summ""," "OFF"")"
ActiveCell.Offset(1, 0).Range("A1:B1").Select

You would normally use "On Error Resume Next" to switch off error trapping
if you are expecting an error and want to ignore it. "On Error Goto 0"
switches error trapping back on ... so that your code will fail if you get
an error you weren't expecting.

I'd suggest you read up on the error trapping options. You could, for
example, use "On Error Goto Label" to process the error out of line.

Regards

Trevor


"MacroLearning" wrote in message
...
I am running a macro that will capture data on pivot tables and enter it
onto
a template. Some months, for example "Europe" will not come up on the
pivot
table because there was no activity to report, but I still need a zero in
place of a #REF when there is nothing to capture.

When I include the On Error Go To 0, nothing happens, It will still come
up
with #REF. Can anyone help with this?

Workbooks.Open ( _
"C:\Automated\Pivots.xls")
Windows("Master.xls").Activate
' Europe
-----On Error GoTo 0 <<<<------
Sheets("Europe").Select
ActiveCell.Offset(0, 0).Range("A1:B1").Select
ActiveCell.FormulaR1C1 = _

"=GETPIVOTDATA(""ID"",'[Pivots.xls]Mthly_Ts'!R1C1,""Breakdown"",""Europe"",""Summ""," "OFF"")"
ActiveCell.Offset(1, 0).Range("A1:B1").Select

I think the problem lies with the pivot table producing the #REF in the
first place. The field properties should be modified to handle the absence
of data with perhaps an IF statement to produce a "0" when data = "".

Mike F
"Trevor Shuttleworth" wrote in message
...
You would normally use "On Error Resume Next" to switch off error trapping
if you are expecting an error and want to ignore it. "On Error Goto 0"
switches error trapping back on ... so that your code will fail if you get
an error you weren't expecting.

I'd suggest you read up on the error trapping options. You could, for
example, use "On Error Goto Label" to process the error out of line.

Regards

Trevor


"MacroLearning" wrote in message
...
I am running a macro that will capture data on pivot tables and enter it
onto
a template. Some months, for example "Europe" will not come up on the
pivot
table because there was no activity to report, but I still need a zero in
place of a #REF when there is nothing to capture.

When I include the On Error Go To 0, nothing happens, It will still come
up
with #REF. Can anyone help with this?

Workbooks.Open ( _
"C:\Automated\Pivots.xls")
Windows("Master.xls").Activate
' Europe
-----On Error GoTo 0 <<<<------
Sheets("Europe").Select
ActiveCell.Offset(0, 0).Range("A1:B1").Select
ActiveCell.FormulaR1C1 = _

"=GETPIVOTDATA(""ID"",'[Pivots.xls]Mthly_Ts'!R1C1,""Breakdown"",""Europe"",""Summ""," "OFF"")"
ActiveCell.Offset(1, 0).Range("A1:B1").Select

I tried to add an IF statement after the formula runs, it gets hung up. The
field will not show up on the Pivot table if it does not show up on report.
However the field name is on the template. when the field name is not found
on the pivot table I'd like it to enter a "0" instead. This is what I have
so far, with no avail.

Workbooks.Open ( _
"C:\Automated\Pivots.xls")
Windows("Master.xls").Activate
' Europe
Sheets("Europe").Select
ActiveCell.Offset(0, 0).Range("A1:B1").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""ID"",'[Pivots.xls]Mthly_Ts'!R1C1,""Breakdown"",""Europe"",""Summ""," "OFF"")"
ActiveCell.Offset(1, 0).Range("A1:B1").Select
Selection.SpecialCells(XlSpecialCellsValue.xlError s).Value = 0

Also:
ActiveCell.Offset(0, 0).Range("A1:B1").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""ID"",'[Pivots.xls]Mthly_Ts'!R1C1,""Breakdown"",""Europe"",""Summ""," "OFF"")"
ActiveCell.Offset(1, 0).Range("A1:B1").Select
If ActiveCell <0 Then cell.value = 0





"Mike Fogleman" wrote:

I think the problem lies with the pivot table producing the #REF in the
first place. The field properties should be modified to handle the absence
of data with perhaps an IF statement to produce a "0" when data = "".

Mike F
"Trevor Shuttleworth" wrote in message
...
You would normally use "On Error Resume Next" to switch off error trapping
if you are expecting an error and want to ignore it. "On Error Goto 0"
switches error trapping back on ... so that your code will fail if you get
an error you weren't expecting.

I'd suggest you read up on the error trapping options. You could, for
example, use "On Error Goto Label" to process the error out of line.

Regards

Trevor


"MacroLearning" wrote in message
...
I am running a macro that will capture data on pivot tables and enter it
onto
a template. Some months, for example "Europe" will not come up on the
pivot
table because there was no activity to report, but I still need a zero in
place of a #REF when there is nothing to capture.

When I include the On Error Go To 0, nothing happens, It will still come
up
with #REF. Can anyone help with this?

Workbooks.Open ( _
"C:\Automated\Pivots.xls")
Windows("Master.xls").Activate
' Europe
-----On Error GoTo 0 <<<<------
Sheets("Europe").Select
ActiveCell.Offset(0, 0).Range("A1:B1").Select
ActiveCell.FormulaR1C1 = _

"=GETPIVOTDATA(""ID"",'[Pivots.xls]Mthly_Ts'!R1C1,""Breakdown"",""Europe"",""Summ""," "OFF"")"
ActiveCell.Offset(1, 0).Range("A1:B1").Select