Skip over a particular error

[Helen]

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

[Die_Another_Day]

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").PivotFields("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

[Helen]

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").PivotFields("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
>
>