Sheet Add/Delete and Build Pivot Table on the Fly

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---
 
M

MDubbelboer

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---
Click to expand...

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
 
J

Jim Thomlinson

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:=xlDatabase, SourceData:= _
.Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="[East.xls]PivotSheet!R2C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
 
R

ryguy7272

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:=xlDatabase, 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").DataPivotField
.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 said:
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---
Click to expand...

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
Click to expand...
 
R

ryguy7272

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 said:
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:=xlDatabase, 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").DataPivotField
.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 said:
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---
Click to expand...

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
Click to expand...
Click to expand...
 
R

ryguy7272

I can’t 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 can’t 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:=xlDatabase, SourceData:= _
ActiveSheet.Range("A1").CurrentRegion).CreatePivotTable _
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").DataPivotField
.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 said:
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 said:
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:=xlDatabase, 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").DataPivotField
.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 said:
On Aug 8, 9:04 am, ryguy7272 <[email protected]>
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:=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---

--
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

[Microsoft][ODBC Driver Manager] error 3
Pivot Table from MS Access Database 3
Please help! Cannot open pivotTable source file.... 2
Pivot Table - change page field help 23
macro skips a command 4
Updating pivot items on 3 pivot tables contained on one sheet 2
Macro pt.RefreshTable 2
update pivot fields VBA 1

Top