I'm using a macro to manipulate a series of pivot tables.

The macro uses a cell reference to decide which project to look for within
the pivot tables, but it is possible that the project does not exist within
all of them. So I want to get the macro to skip to the end of that section's
code if the pivot table in question doesn't contain the project. At the
moment I get an error code:

Runtime error '1004'
Unable to set the_Default property of the PivotItem class

I have looked into it myself and I think I might need to use error handling,
but when I tried to put the code in VBA fell over.

Could you point out what I should be putting in where?

There are 6 other sections like this one, each of them relating to a
different worksheet and pivot table.

mySourceData = Workbooks(HomeWS).Sheets("Imported Data").Range("F7").Value

Sheets("Department").Select
Range("B6").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project").CurrentPage =
mySourceData

myRows = Selection.CurrentRegion.Rows.Count

Range("B6:D" & myRows + 3).Select
Selection.ShowDetail = False

myRows2 = Selection.CurrentRegion.Rows.Count

Range("B6:D" & myRows2 + 2).Select
Selection.Copy

Windows(HomeWS).Activate
Worksheets("Imported Data").Activate
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False

SourceWS.Worksheets("Department").Activate
Selection.ShowDetail = True

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project").CurrentPage =
"(All)"

Many thanks for your help

You could user error handling, however I would suggest creating an
object and setting that object to the field you want. This allows you
to check if it "is Nothing", like so:
Dim Pf As PivotField
Set Pf = ActiveSheet.PivotTables("MyPivotTable").PivotField s("MyField")
If Pf is Nothing Then
'Not There
Else
'Is There
End If

If you still want to just trap the error, it is fairly easy:
On error resume next
'Statement that causes error
if err.number = 1004 then
'error was found
err.clear 'clears the error
else
'no error
end if
On error goto 0

HTH

Die_Another_Day
Helen wrote:
I'm using a macro to manipulate a series of pivot tables.

The macro uses a cell reference to decide which project to look for within
the pivot tables, but it is possible that the project does not exist within
all of them. So I want to get the macro to skip to the end of that section's
code if the pivot table in question doesn't contain the project. At the
moment I get an error code:

Runtime error '1004'
Unable to set the_Default property of the PivotItem class

I have looked into it myself and I think I might need to use error handling,
but when I tried to put the code in VBA fell over.

Could you point out what I should be putting in where?

There are 6 other sections like this one, each of them relating to a
different worksheet and pivot table.

mySourceData = Workbooks(HomeWS).Sheets("Imported Data").Range("F7").Value

Sheets("Department").Select
Range("B6").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project").CurrentPage =
mySourceData

myRows = Selection.CurrentRegion.Rows.Count

Range("B6:D" & myRows + 3).Select
Selection.ShowDetail = False

myRows2 = Selection.CurrentRegion.Rows.Count

Range("B6:D" & myRows2 + 2).Select
Selection.Copy

Windows(HomeWS).Activate
Worksheets("Imported Data").Activate
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False

SourceWS.Worksheets("Department").Activate
Selection.ShowDetail = True

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project").CurrentPage =
"(All)"

Many thanks for your help

Thanks.

I think I'll use the error handling. If I have any problems I'll pop back.



"Die_Another_Day" wrote:

You could user error handling, however I would suggest creating an
object and setting that object to the field you want. This allows you
to check if it "is Nothing", like so:
Dim Pf As PivotField
Set Pf = ActiveSheet.PivotTables("MyPivotTable").PivotField s("MyField")
If Pf is Nothing Then
'Not There
Else
'Is There
End If

If you still want to just trap the error, it is fairly easy:
On error resume next
'Statement that causes error
if err.number = 1004 then
'error was found
err.clear 'clears the error
else
'no error
end if
On error goto 0

HTH

Die_Another_Day
Helen wrote:
I'm using a macro to manipulate a series of pivot tables.

The macro uses a cell reference to decide which project to look for within
the pivot tables, but it is possible that the project does not exist within
all of them. So I want to get the macro to skip to the end of that section's
code if the pivot table in question doesn't contain the project. At the
moment I get an error code:

Runtime error '1004'
Unable to set the_Default property of the PivotItem class

I have looked into it myself and I think I might need to use error handling,
but when I tried to put the code in VBA fell over.

Could you point out what I should be putting in where?

There are 6 other sections like this one, each of them relating to a
different worksheet and pivot table.

mySourceData = Workbooks(HomeWS).Sheets("Imported Data").Range("F7").Value

Sheets("Department").Select
Range("B6").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project").CurrentPage =
mySourceData

myRows = Selection.CurrentRegion.Rows.Count

Range("B6:D" & myRows + 3).Select
Selection.ShowDetail = False

myRows2 = Selection.CurrentRegion.Rows.Count

Range("B6:D" & myRows2 + 2).Select
Selection.Copy

Windows(HomeWS).Activate
Worksheets("Imported Data").Activate
Range("A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False

SourceWS.Worksheets("Department").Activate
Selection.ShowDetail = True

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project").CurrentPage =
"(All)"

Many thanks for your help