R
ryguy7272
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:=xlDatabase, SourceData:= _
ActiveSheet.Range("A1").CurrentRegion).CreatePivotTable _
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---
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:=xlDatabase, SourceData:= _
ActiveSheet.Range("A1").CurrentRegion).CreatePivotTable _
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---