Try this... It is roughly the same as your code without the references to
activesheet or activebook which you generaly want to avoid...

on error resume next
set NewSht = Worksheets("Pivot Sheet")
on error resume next
if not newsht is nothing then
applicaton.dispalyalerts = false
newsht.delete
application.displayalerts = true
end if
set newsht = nothing
Set NewSht = Worksheets.Add
with NewSht
.Name = "PivotSheet"
'I am a little unclear where you source data is???
'Does the pivot go on the new sheet???
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabas e, SourceData:= _
.Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="[East.xls]PivotSheet!R2C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

--
HTH...

Jim Thomlinson


"ryguy7272" wrote:

I have a little dilemma. I can't figure out what to do first. I have to
test my workbook to see if a specific sheet exists (named 'Pivot Sheet') and
delete it if it does exist then create a new sheet and name it PivotSheet.
Also, I have to select data from an 'ActiveSheet', using
ActiveSheet.Select

The reason for this is because I have data on four sheets and I want to
dynamically build a Pivot Table, using the same headers and same structure,
but the data is for four different people. I plan to have four
CommandButtons on the four different sheets, all linked to the same macro.
This is why ActiveSheet.Select seems to be the obvious choice.
Anyway, this was working fine for a while, but yesterday one of the VPs said
he wanted to see the Pivot Table on a new sheet, not the same sheet as the
data. So, long story short, how can I use ActiveSheet.Select and also test
for the existence of a sheet and delete it if it exists, or build it if it
doesn't exist?

Most of my code is listed below (without the PivotFields listed here):
Dim NewSht As Worksheet
Dim pt As PivotTable
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.TableRange2.Clear
Next pt
Next ws


For Each ws In ThisWorkbook.Worksheets
If ws.Name = "PivotSheet" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Next ws

Set NewSht = Worksheets.Add
NewSht.Name = "PivotSheet"

ActiveSheet.Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
ActiveSheet.Range("A1").CurrentRegion).CreatePivot Table _
TableDestination:="[East.xls]PivotSheet!R2C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

I just can't seem to figure out the structure of the program. If someone
could help I would really appreciate it!!


Regards,
Ryan---


--
RyGuy