Try something more like this:

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Database")


On Error Goto PivotError

Set PT = PTCache.CreatePivotTable(TableDestination:="",
TableName:="PercentTable")
On Error Goto 0
With PT
.PivotFields("Week Ending").Orientation = xlColumnField
.PivotFields("Target Early % Comp.").Orientation = xlDataField
.PivotFields("Target Late % Comp.").Orientation = xlDataField
.PivotFields("Target Planned % Comp.").Orientation = xlDataField
.ColumnGrand = False
.RowGrand = False

End With
On Error Goto SheetNameError
ActiveSheet.Name = "Pivot"
Exit Sub
PivotError:
MsgBox "Did you Copy the Export and Run Cleanup?" _
& vbCrLf & "If not then run them before running this Macro"
_
& vbCrLf & " If you have then you must Delete" _
& vbCrLf & "the (Pivot) Worksheet before running this Macro"
exit sub
SheetNameError:
'Not too sure how you want to handle this error...
End Sub

--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

Thanks for the reply Jim. I'm using a drop down menu to run the macros.


"Jim Thomlinson" wrote in message
...
This assumes that you are using command buttons to run your macro's but
why
not toggle the enabled property of the button. By default set the property
to
false so that the sub can not be run until data is retrieved. When the
data
is retrieved from the database then set the property to true. Now the user
can create the pivot with your sub. When the create pivot sub is run set
the
property back to false and now it can not be run twice. This should put an
end to your errors. Just a different way to fix the problem.
--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

Below is a sample of my code. During testing I determined that , there
are
two way's the user can create an error; by not retrieving the data for
the
data base and by running the Macro a second time. The macro is designed
to
create a pivot table on the fly (once). What I need help with is
revising
the code to only show the message box if there is an error and then
exiting
the sub once they click the okay button on the message box. Now I get
the
error message even if no error occurs while running the Sub. After I
click
okay it finished the Sub Procedure.

Joel Mills

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Database")


On Error Resume Next

MsgBox "Did you Copy the Export and Run Cleanup?" _
& vbCrLf & "If not then run them before running this
Macro"
_
& vbCrLf & " If you have then you must Delete" _
& vbCrLf & "the (Pivot) Worksheet before running this
Macro"

Set PT = PTCache.CreatePivotTable(TableDestination:="",
TableName:="PercentTable")

With PT
.PivotFields("Week Ending").Orientation = xlColumnField
.PivotFields("Target Early % Comp.").Orientation = xlDataField
.PivotFields("Target Late % Comp.").Orientation = xlDataField
.PivotFields("Target Planned % Comp.").Orientation = xlDataField
.ColumnGrand = False
.RowGrand = False

End With

ActiveSheet.Name = "Pivot"
End Sub