Excel PivotTable Lookup Overwrites Data

[Zwicky]

I have a pivot table in Excel that references external data in Access. The pivot table is changed via a worksheet which acts as a user form. The user selects from a list and vba code causes the pivot table values to change. One of the selections consists of three consecutive lists which produces a concatenated key which is then used to access the pivot table (ca + cg + csg gives ckey).

The problem is that the options in the pivot table (under CKey) keep getting mixed up when I choose them from the user form and then start returning the wrong data. So, for example, instead of having the options 1,2,3,4 etc in the pivot table options, the list might become 3,2,4,1 and when 1 is selected, it returns the data for 3 instead.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim caRange As Range
Dim cgRange As Range
Dim csgRange As Range
Set caRange = Range("CA")
Set cgRange = Range("CG")
Set csgRange = Range("CSG")

If Union(Target, caRange).Address = caRange.Address Then
cgRange.ClearContents
csgRange.ClearContents

End If

If Union(Target, cgRange).Address = cgRange.Address Then
csgRange.ClearContents

End If

Dim ckey As Variant
Range("CKey").Select
ckey = Selection
Sheets("Pivot").PivotTables("PivotTable3").PivotFields("C Key").CurrentPage = ckey
Sheets("0405 Data").PivotTables("PivotTable4").PivotFields("C Key").CurrentPage = ckey

Dim gender As Variant
Range("Gender").Select
gender = Selection
Sheets("Pivot").PivotTables("PivotTable3").PivotFields("Gender").CurrentPage = gender
Sheets("0405 Data").PivotTables("PivotTable4").PivotFields("Gender").CurrentPage = gender
Sheets("Pop Data").PivotTables("PivotTable1").PivotFields("Gender").CurrentPage = gender

Dim indigenous As Variant
Range("indigenous").Select
indigenous = Selection
Sheets("Pivot").PivotTables("PivotTable3").PivotFields("Indigenous Status").CurrentPage = indigenous
Sheets("0405 Data").PivotTables("PivotTable4").PivotFields("Indigenous Status").CurrentPage = indigenous
Sheets("Pop Data").PivotTables("PivotTable1").PivotFields("Indigenous Status").CurrentPage = indigenous

Dim drg As Variant
Range("DRG").Select
drg = Selection
Sheets("Pivot").PivotTables("PivotTable3").PivotFields("DRG Type").CurrentPage = drg
Sheets("0405 Data").PivotTables("PivotTable4").PivotFields("DRG Type").CurrentPage = drg

Dim emnl As Variant
Range("EMNL").Select
emnl = Selection
Sheets("Pivot").PivotTables("PivotTable3").PivotFields("EMNL").CurrentPage = emnl
Sheets("0405 Data").PivotTables("PivotTable4").PivotFields("EMNL").CurrentPage = emnl

Dim stay As Variant
Range("StayType").Select
stay = Selection
Sheets("Pivot").PivotTables("PivotTable3").PivotFields("Stay Type").CurrentPage = stay
Sheets("0405 Data").PivotTables("PivotTable4").PivotFields("Stay Type").CurrentPage = stay

Sheets("Vary").Select
Range("A1").Select

End Sub


Is the pivot table statement wrong?
Thanks to anyone who can help me.
This is my first time programming so I'm pretty lost.

 

[SkipVought]

Hi,

Are you refering to OPTIONS, as in a dialog, or OPTIONS as a list of values within your pivot table?

If it the latter, then take a look at the Field Settings/Advanced/AutoSort options.

Skip,
[sub]
[red][/red]
[/sub]

 

[Zwicky]

I'm not sure what you mean.
The list that the user chooses from is simply a validated list refering to another worksheet with the names typed in. A vlookup changes the name to an id which is then concatenated with the others and this range is what is looked for in the pivot table field.
I had a look under field settings and there is no 'advanced' option. Perhaps because I'm using Excel 2000?