Pivot Table Sort
 

In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.

Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.

Erin

Pivot Table Sort
 

What are the items? If you move them to the end, when do they move back
to the top?


Erin Searfoss wrote:
In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.

Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.

Erin


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Sort
 

The table that changes just about everytime I open the file includes items as
follows (as rows):

EH
FB
FP
HA
....
LR
MA

where MA is the offending item which keeps rising to the top. I have two
similar files and sometimes it happens on one and not the other.

The other table includes (as columns):

Office Salary
Office Overtime
Contract Labor
Stationary and Supplies
Telephone
....
Rent

where Office Salary, Office Overtime, and Contract Labor are the offending
items, moving to the end. I think this has only happened once. Unfortunately
it may have escaped my notice in the past.

I've been using these two files for almost 7 years and this just started a
few months ago. We didn't change Excel versions, but the firm is always
running updates. My co-worker hasn't noticed it, but I don't know how often
she has accessed the file in the last few months.

Any ideas?

"Debra Dalgleish" wrote:

What are the items? If you move them to the end, when do they move back
to the top?


Erin Searfoss wrote:
In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.

Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.

Erin


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Sort
 

Important point I forgot to mention... The pivot table is refreshed upon
opening with the following macro which looks for a specific Access file in
the same folder and updates only if it finds the file. I don't seem to have
a problem when the Access file is not in the same folder and the macro exits.

Also the file now is not calculating. Earlier today while the file was open
I switched to Manual Calc to work on another file. I've returned to
Automatic Calc. The other files calc fine, but not this one. This is true
whether the Open macro runs or not. Any idea what could cause that. I've
closed Excel once, but it didn't seem to help. Any ideas about that one?

Private Sub Workbook_Open()

Dim PTCache As PivotCache
Dim PT As PivotTable

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)

' Path to database file
dbFile = ThisWorkbook.Path & "\variance2007.mdb"
If Dir(dbFile) = "" Then Exit Sub

' Connection String
ConString = "ODBC;DSN=MS Access Database;DBQ=" & dbFile

' QueryString
queryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\variance2007`.Data Data"

With PTCache
.Connection = ConString
.CommandText = queryString
.MaintainConnection = False
End With

' Create pivot table
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").PivotSelect "", xlDataAndLabel
Selection.ClearContents
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("Variance Pivots").Range("A8"), _
TableName:="ActualTable")

' Add fields
With PT
' Add fields
.PivotFields("Code").Orientation = xlRowField
.PivotFields("Code").Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
.PivotFields("Cat").Orientation = xlRowField
With .PivotFields("Dom")
.Orientation = xlColumnField
.PivotItems("LA").Position = 1
.PivotItems("OC").Position = 2
.PivotItems("DC").Position = 3
.PivotItems("SD").Position = 4
.PivotItems("CH").Position = 5
.PivotItems("NY").Position = 6
.PivotItems("SF").Position = 7
.PivotItems("NJ").Position = 8
.PivotItems("SV").Position = 9
.PivotItems("VA").Position = 10
.PivotItems("Int").Position = 11
.PivotItems("GSO").Position = 12
End With
.PivotFields("Period").Orientation = xlPageField
.PivotFields("Net").Orientation = xlDataField

End With


Sheets("Trend Pivot").Activate
Range("Pivot").Select
strTable = "[" & ActiveWorkbook.Name & "]Variance Pivots!ActualTable"
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable,
SourceData:=strTable
With ActiveSheet.PivotTables("TrendTable").PivotCache
.RefreshOnFileOpen = False
.SavePassword = True
End With
ActiveSheet.PivotTables("TrendTable").RefreshTable
ActiveSheet.PivotTables("TrendTable").AddFields RowFields:="Period", _
ColumnFields:="Cat", PageFields:="Loc"
Application.CommandBars("PivotTable").Visible = False
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").SaveData = False
Application.CommandBars("PivotTable").Visible = False

Sheets("Total").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


"Erin Searfoss" wrote:

The table that changes just about everytime I open the file includes items as
follows (as rows):

EH
FB
FP
HA
...
LR
MA

where MA is the offending item which keeps rising to the top. I have two
similar files and sometimes it happens on one and not the other.

The other table includes (as columns):

Office Salary
Office Overtime
Contract Labor
Stationary and Supplies
Telephone
...
Rent

where Office Salary, Office Overtime, and Contract Labor are the offending
items, moving to the end. I think this has only happened once. Unfortunately
it may have escaped my notice in the past.

I've been using these two files for almost 7 years and this just started a
few months ago. We didn't change Excel versions, but the firm is always
running updates. My co-worker hasn't noticed it, but I don't know how often
she has accessed the file in the last few months.

Any ideas?

"Debra Dalgleish" wrote:

What are the items? If you move them to the end, when do they move back
to the top?


Erin Searfoss wrote:
In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.

Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.

Erin


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Sort
 

Have you checked again, to make sure it's still set on Automatic Calc? A
workbook will use the setting from the first workbook that's opened
during an Excel session, so it may have been unintentionally reset to
Manual.

For the macro, could you set the order of the Code field items, just as
you're setting the Dom field items?

You may also want to set the pivot tables to prevent missing items from
staying in the pivot cache. That may be why some items are out of place.
There's information he

http://www.contextures.com/xlPivot04.html

Erin Searfoss wrote:
Important point I forgot to mention... The pivot table is refreshed upon
opening with the following macro which looks for a specific Access file in
the same folder and updates only if it finds the file. I don't seem to have
a problem when the Access file is not in the same folder and the macro exits.

Also the file now is not calculating. Earlier today while the file was open
I switched to Manual Calc to work on another file. I've returned to
Automatic Calc. The other files calc fine, but not this one. This is true
whether the Open macro runs or not. Any idea what could cause that. I've
closed Excel once, but it didn't seem to help. Any ideas about that one?

Private Sub Workbook_Open()

Dim PTCache As PivotCache
Dim PT As PivotTable

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)

' Path to database file
dbFile = ThisWorkbook.Path & "\variance2007.mdb"
If Dir(dbFile) = "" Then Exit Sub

' Connection String
ConString = "ODBC;DSN=MS Access Database;DBQ=" & dbFile

' QueryString
queryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\variance2007`.Data Data"

With PTCache
.Connection = ConString
.CommandText = queryString
.MaintainConnection = False
End With

' Create pivot table
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").PivotSelect "", xlDataAndLabel
Selection.ClearContents
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("Variance Pivots").Range("A8"), _
TableName:="ActualTable")

' Add fields
With PT
' Add fields
.PivotFields("Code").Orientation = xlRowField
.PivotFields("Code").Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
.PivotFields("Cat").Orientation = xlRowField
With .PivotFields("Dom")
.Orientation = xlColumnField
.PivotItems("LA").Position = 1
.PivotItems("OC").Position = 2
.PivotItems("DC").Position = 3
.PivotItems("SD").Position = 4
.PivotItems("CH").Position = 5
.PivotItems("NY").Position = 6
.PivotItems("SF").Position = 7
.PivotItems("NJ").Position = 8
.PivotItems("SV").Position = 9
.PivotItems("VA").Position = 10
.PivotItems("Int").Position = 11
.PivotItems("GSO").Position = 12
End With
.PivotFields("Period").Orientation = xlPageField
.PivotFields("Net").Orientation = xlDataField

End With


Sheets("Trend Pivot").Activate
Range("Pivot").Select
strTable = "[" & ActiveWorkbook.Name & "]Variance Pivots!ActualTable"
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable,
SourceData:=strTable
With ActiveSheet.PivotTables("TrendTable").PivotCache
.RefreshOnFileOpen = False
.SavePassword = True
End With
ActiveSheet.PivotTables("TrendTable").RefreshTable
ActiveSheet.PivotTables("TrendTable").AddFields RowFields:="Period", _
ColumnFields:="Cat", PageFields:="Loc"
Application.CommandBars("PivotTable").Visible = False
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").SaveData = False
Application.CommandBars("PivotTable").Visible = False

Sheets("Total").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


"Erin Searfoss" wrote:


The table that changes just about everytime I open the file includes items as
follows (as rows):

EH
FB
FP
HA
...
LR
MA

where MA is the offending item which keeps rising to the top. I have two
similar files and sometimes it happens on one and not the other.

The other table includes (as columns):

Office Salary
Office Overtime
Contract Labor
Stationary and Supplies
Telephone
...
Rent

where Office Salary, Office Overtime, and Contract Labor are the offending
items, moving to the end. I think this has only happened once. Unfortunately
it may have escaped my notice in the past.

I've been using these two files for almost 7 years and this just started a
few months ago. We didn't change Excel versions, but the firm is always
running updates. My co-worker hasn't noticed it, but I don't know how often
she has accessed the file in the last few months.

Any ideas?

"Debra Dalgleish" wrote:


What are the items? If you move them to the end, when do they move back
to the top?


Erin Searfoss wrote:

In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.

Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.

Erin


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Sort
 

Looks like that's what I'm going to have to do on the pivot tables. Still
wish I knew why it suddenly started acting up, but I guess life is full of
unsolved mysteries.

It appears I'm going to have to go back to an earlier version and make all
the 2007 updates again. I still can't get the darn thing to calculate. F9
doesn't work, and I've even tried forcing it through VBA - nothing. Another
of life's mysteries.

Thanks for trying.

Erin

"Debra Dalgleish" wrote:

Have you checked again, to make sure it's still set on Automatic Calc? A
workbook will use the setting from the first workbook that's opened
during an Excel session, so it may have been unintentionally reset to
Manual.

For the macro, could you set the order of the Code field items, just as
you're setting the Dom field items?

You may also want to set the pivot tables to prevent missing items from
staying in the pivot cache. That may be why some items are out of place.
There's information he

http://www.contextures.com/xlPivot04.html

Erin Searfoss wrote:
Important point I forgot to mention... The pivot table is refreshed upon
opening with the following macro which looks for a specific Access file in
the same folder and updates only if it finds the file. I don't seem to have
a problem when the Access file is not in the same folder and the macro exits.

Also the file now is not calculating. Earlier today while the file was open
I switched to Manual Calc to work on another file. I've returned to
Automatic Calc. The other files calc fine, but not this one. This is true
whether the Open macro runs or not. Any idea what could cause that. I've
closed Excel once, but it didn't seem to help. Any ideas about that one?

Private Sub Workbook_Open()

Dim PTCache As PivotCache
Dim PT As PivotTable

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)

' Path to database file
dbFile = ThisWorkbook.Path & "\variance2007.mdb"
If Dir(dbFile) = "" Then Exit Sub

' Connection String
ConString = "ODBC;DSN=MS Access Database;DBQ=" & dbFile

' QueryString
queryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\variance2007`.Data Data"

With PTCache
.Connection = ConString
.CommandText = queryString
.MaintainConnection = False
End With

' Create pivot table
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").PivotSelect "", xlDataAndLabel
Selection.ClearContents
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("Variance Pivots").Range("A8"), _
TableName:="ActualTable")

' Add fields
With PT
' Add fields
.PivotFields("Code").Orientation = xlRowField
.PivotFields("Code").Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
.PivotFields("Cat").Orientation = xlRowField
With .PivotFields("Dom")
.Orientation = xlColumnField
.PivotItems("LA").Position = 1
.PivotItems("OC").Position = 2
.PivotItems("DC").Position = 3
.PivotItems("SD").Position = 4
.PivotItems("CH").Position = 5
.PivotItems("NY").Position = 6
.PivotItems("SF").Position = 7
.PivotItems("NJ").Position = 8
.PivotItems("SV").Position = 9
.PivotItems("VA").Position = 10
.PivotItems("Int").Position = 11
.PivotItems("GSO").Position = 12
End With
.PivotFields("Period").Orientation = xlPageField
.PivotFields("Net").Orientation = xlDataField

End With


Sheets("Trend Pivot").Activate
Range("Pivot").Select
strTable = "[" & ActiveWorkbook.Name & "]Variance Pivots!ActualTable"
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable,
SourceData:=strTable
With ActiveSheet.PivotTables("TrendTable").PivotCache
.RefreshOnFileOpen = False
.SavePassword = True
End With
ActiveSheet.PivotTables("TrendTable").RefreshTable
ActiveSheet.PivotTables("TrendTable").AddFields RowFields:="Period", _
ColumnFields:="Cat", PageFields:="Loc"
Application.CommandBars("PivotTable").Visible = False
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").SaveData = False
Application.CommandBars("PivotTable").Visible = False

Sheets("Total").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


"Erin Searfoss" wrote:


The table that changes just about everytime I open the file includes items as
follows (as rows):

EH
FB
FP
HA
...
LR
MA

where MA is the offending item which keeps rising to the top. I have two
similar files and sometimes it happens on one and not the other.

The other table includes (as columns):

Office Salary
Office Overtime
Contract Labor
Stationary and Supplies
Telephone
...
Rent

where Office Salary, Office Overtime, and Contract Labor are the offending
items, moving to the end. I think this has only happened once. Unfortunately
it may have escaped my notice in the past.

I've been using these two files for almost 7 years and this just started a
few months ago. We didn't change Excel versions, but the firm is always
running updates. My co-worker hasn't noticed it, but I don't know how often
she has accessed the file in the last few months.

Any ideas?

"Debra Dalgleish" wrote:


What are the items? If you move them to the end, when do they move back
to the top?


Erin Searfoss wrote:

In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.

Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.

Erin


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html