How to change pivot table field based on a range selection in the most optim way

[siddharth.p]

I am very new to Excel VBA , so kindly excuse me if its a basic question.

I currently have prepared a code which useful for a system data analysis. The issue I am having is that the code is that its huge , request help in reducing my code size for the below area


Quick brief -

There is a home page on which there is a Form Control combo box linked to a range & it has one cell as the index ("CA1").


Based on the combo box selection a pivot table with the appropriate field is created


On the basis of the index value changing the below is performed


Issue - Huge code running into multiple lines , if I could make this portion crisp and short it would be great help.

Thank you


----------------------------------------------------------------

' Creation of pivot tables

Sheets("Array_Raw_Data").Select

Range("A1").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"Array_Raw_Data!R1C1:R52993C56", Version:=xlPivotTableVersion14).CreatePivotTable _

TableDestination:="Array_Analysis_Pane!R1C3", TableName:="PivotTable1", _

DefaultVersion:=xlPivotTableVersion14

Sheets("Array_Analysis_Pane").Select

Cells(1, 3).Select


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Array")

.Orientation = xlColumnField

.Position = 1

End With


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")

.Orientation = xlRowField

.Position = 1

End With


' Read I/O fieds 1 to 3

If Sheets("Home").Range("CA1").Value = "1" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Read I/O Rate (normal)"), _

" Read I/O Rate (normal)", xlSum


ElseIf Sheets("Home").Range("CA1").Value = "2" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Read I/O Rate (sequential)"), _

" Read I/O Rate (sequential)", xlSum


ElseIf Sheets("Home").Range("CA1").Value = "3" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Read I/O Rate (overall)"), _

" Read I/O Rate (overall)", xlSum


' Write I/O Rate 4 to 6

ElseIf Sheets("Home").Range("CA1").Value = "4" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Write I/O Rate (normal)"), _

" Write I/O Rate (normal)", xlSum


ElseIf Sheets("Home").Range("CA1").Value = "5" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Write I/O Rate (sequential)"), _

" Write I/O Rate (sequential)", xlSum


ElseIf Sheets("Home").Range("CA1").Value = "6" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Write I/O Rate (overall)"), _

" Write I/O Rate (overall)", xlSum


' Total I/O Rate 7 to 9

ElseIf Sheets("Home").Range("CA1").Value = "7" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Total I/O Rate (normal)"), _

" Total I/O Rate (normal)", xlSum


"multiple lines like above

---------------------------------------------

 

[Luke M]

Rather than getting this index number, why not just user the Field name that the user is providing? (either directly, or use a vlookup). Then you can simply to something like:

[pre]

----------------------------------------------------------------
' Creation of pivot tables
Sheets("Array_Raw_Data").Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Array_Raw_Data!R1C1:R52993C56", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Array_Analysis_Pane!R1C3", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
Sheets("Array_Analysis_Pane").Select
Cells(1, 3).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Array")
.Orientation = xlColumnField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
.Orientation = xlRowField
.Position = 1
End With

Dim UserChoice As String
'Rather than the index number, return the field you want
UserChoice = Range("CA1")

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(UserChoice), _
" " & "Write I/O Rate (sequential)", xlSum

---------------------------------------------

[/pre]