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

On Aug 8, 9:04*am, 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

Activesheet refers to the sheet that is currently active
For Each ws In ThisWorkbook.Worksheets takes you through all the
worksheets and will leave you on the last worksheet, which will now be
activesheet.

It would make more sense to refer to it as "worksheets("Name of
sheet")"
if it does indeed change every time, you can create a new ws variable
and set it to activesheet at the beginning
set newws = activesheet
run rest of code
newws.select
range("A1").select
rest of code

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

thanks for the code Jim! That builds a sheet named PivotSheet, if it doesn't
exist, but it doesn't delete it if it already exists. Also, it doesn't build
the Pivot Table. Something gets lost in memory, or it is never set in
memory, because all I have is a blanks sheet named PivotSheet, and if I rerun
the code I get Sheet1, then Sheet2, then Sheet3, etc.

Any thoughts on that? I'm still trying a few things too.
Here is the code in it's entirety:
Sub BuildPT()

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
End With


ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Director", _
"Sales Rep", "Related Company", "Data"), ColumnFields:="Quarter",
PageFields _
:="Source"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class 1")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class
2").Orientation = _
xlDataField
Windows("August 6.xls").Activate
Windows("East.xls").Activate
Range("D4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Quarter[All]",
xlLabelOnly _
, True
Range("E3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Quarter")
.Orientation = xlColumnField
.Position = 1
End With
Columns("D:I").Select
Selection.Style = "Currency"

Cells.Select
Cells.EntireColumn.AutoFit


End Sub


Regards,
Ryan--

--
RyGuy


"MDubbelboer" wrote:

On Aug 8, 9:04 am, 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

Activesheet refers to the sheet that is currently active
For Each ws In ThisWorkbook.Worksheets takes you through all the
worksheets and will leave you on the last worksheet, which will now be
activesheet.

It would make more sense to refer to it as "worksheets("Name of
sheet")"
if it does indeed change every time, you can create a new ws variable
and set it to activesheet at the beginning
set newws = activesheet
run rest of code
newws.select
range("A1").select
rest of code

Ok, it was just the space that confused both myself and Excel. Should be
something like this this:

Set NewSht = Worksheets("PivotSheet")


I still can't get Excel to identify the 'ActiveSheet'. I think that Excel
thinks that the PivotSheet is the ActiveSheet; the PivotSheet is always blank
but I would expect the data from the ActiveSheet to go to the PivotSheet with
the appropriate layout, all the appropriate fields, etc. Any thoughts?


Regards,
Ryan--

--
RyGuy


"ryguy7272" wrote:

thanks for the code Jim! That builds a sheet named PivotSheet, if it doesn't
exist, but it doesn't delete it if it already exists. Also, it doesn't build
the Pivot Table. Something gets lost in memory, or it is never set in
memory, because all I have is a blanks sheet named PivotSheet, and if I rerun
the code I get Sheet1, then Sheet2, then Sheet3, etc.

Any thoughts on that? I'm still trying a few things too.
Here is the code in it's entirety:
Sub BuildPT()

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
End With


ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Director", _
"Sales Rep", "Related Company", "Data"), ColumnFields:="Quarter",
PageFields _
:="Source"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class 1")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class
2").Orientation = _
xlDataField
Windows("August 6.xls").Activate
Windows("East.xls").Activate
Range("D4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Quarter[All]",
xlLabelOnly _
, True
Range("E3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Quarter")
.Orientation = xlColumnField
.Position = 1
End With
Columns("D:I").Select
Selection.Style = "Currency"

Cells.Select
Cells.EntireColumn.AutoFit


End Sub


Regards,
Ryan--

--
RyGuy


"MDubbelboer" wrote:

On Aug 8, 9:04 am, 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

Activesheet refers to the sheet that is currently active
For Each ws In ThisWorkbook.Worksheets takes you through all the
worksheets and will leave you on the last worksheet, which will now be
activesheet.

It would make more sense to refer to it as "worksheets("Name of
sheet")"
if it does indeed change every time, you can create a new ws variable
and set it to activesheet at the beginning
set newws = activesheet
run rest of code
newws.select
range("A1").select
rest of code

I cant seem to figure out how to create the PivotSsheet, but remain on the
ActiveSheet, and build the Pivot Table based on the data from the
ActiveSheet. There must be a way, but I just cant seem to do it. If anyone
has any ideas, please send them my way. In the meantime, I am going with
this workaround, which assumes the PivotSheet will always be there and it
will just delete all pivot tables in the Workbook before rebuilding the
PivotTable (form the ActiveSheet) each time the code fires.


Current Solution:
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


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


Sheets("PivotSheet").Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Director", _
"Sales Rep", "Related Company", "Data"), ColumnFields:="Quarter",
PageFields _
:="Source"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class 1")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class
2").Orientation = _
xlDataField


Range("D4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Quarter[All]",
xlLabelOnly _
, True
Range("E3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Quarter")
.Orientation = xlColumnField
.Position = 1
End With
Columns("D:I").Select
Selection.Style = "Currency"

Cells.Select
Cells.EntireColumn.AutoFit

Regards,
Ryan---

--
RyGuy


"ryguy7272" wrote:

Ok, it was just the space that confused both myself and Excel. Should be
something like this this:

Set NewSht = Worksheets("PivotSheet")


I still can't get Excel to identify the 'ActiveSheet'. I think that Excel
thinks that the PivotSheet is the ActiveSheet; the PivotSheet is always blank
but I would expect the data from the ActiveSheet to go to the PivotSheet with
the appropriate layout, all the appropriate fields, etc. Any thoughts?


Regards,
Ryan--

--
RyGuy


"ryguy7272" wrote:

thanks for the code Jim! That builds a sheet named PivotSheet, if it doesn't
exist, but it doesn't delete it if it already exists. Also, it doesn't build
the Pivot Table. Something gets lost in memory, or it is never set in
memory, because all I have is a blanks sheet named PivotSheet, and if I rerun
the code I get Sheet1, then Sheet2, then Sheet3, etc.

Any thoughts on that? I'm still trying a few things too.
Here is the code in it's entirety:
Sub BuildPT()

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
End With


ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Director", _
"Sales Rep", "Related Company", "Data"), ColumnFields:="Quarter",
PageFields _
:="Source"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class 1")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Class
2").Orientation = _
xlDataField
Windows("August 6.xls").Activate
Windows("East.xls").Activate
Range("D4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Quarter[All]",
xlLabelOnly _
, True
Range("E3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Quarter")
.Orientation = xlColumnField
.Position = 1
End With
Columns("D:I").Select
Selection.Style = "Currency"

Cells.Select
Cells.EntireColumn.AutoFit


End Sub


Regards,
Ryan--

--
RyGuy


"MDubbelboer" wrote:

On Aug 8, 9:04 am, 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

Activesheet refers to the sheet that is currently active
For Each ws In ThisWorkbook.Worksheets takes you through all the
worksheets and will leave you on the last worksheet, which will now be
activesheet.

It would make more sense to refer to it as "worksheets("Name of
sheet")"
if it does indeed change every time, you can create a new ws variable
and set it to activesheet at the beginning
set newws = activesheet
run rest of code
newws.select
range("A1").select
rest of code