Sleeper: How Can I create a Pivot table using VBA?

[frade]

Hi :hi:

Here is my code:


Private Sub Worksheet_Change(ByVal target As Range)
Set isect = Application.Intersect(Range("b1"), target)
If isect Is Nothing Then
Exit Sub
Else
Sheets(2).Range("a2:d" & Sheets(2).Range("a65536").End(xlUp).Row).ClearContents
Test = Range("b1").Value
l = 0
c = 0
For i = 1 To 10
For k = 1 To Test 'dilution
For j = 1 To 2
Sheets(2).Range("c2").Offset(l, 0) = k
Sheets(2).Range("c2").Offset(l, -1) = i
Sheets(2).Range("c2").Offset(l, -2) = l + 1
Sheets(2).Range("c2").Offset(l, 1) = j
l = l + 1
Next j
Next k
Next i
End If
Sheets(2).Range("A1") = "ID"
Sheets(2).Range("B1") = "DAY"
Sheets(2).Range("C1") = "NUMBER OF DILUTIONS"
Sheets(2).Range("D1") = "NUM OF REPEAT"
Sheets(2).Range("E1") = "RESULTS"
End Sub


With this code, when an user change the value of one cell
(position B1 on Sheet1), the second page called sheet 2 is automatically updated...four columns (from A to B) are filled and the operator
has just to enter the results in the column E

Now, I would like to create a pivot table with the following positions:

Row Field = Day
Column Field = Number of dilutions
Data items = Results

What can I do?

Thanks a lot

Fran?ois

[Paleo]

Hi fran?ois,

try this:


Dim myPivot As PivotTable
Dim myField As PivotField
Dim myItem As PivotItem
Dim myRange As Range
Set myPivot = Sheets(2).PivotTableWizard
Set myField = myPivot.Pivot-Fields("Results")
myField.Orientation = xlDataField
Set myField = myPivot.Pivot-Fields("Day")
myField.Orientation = xlRowField
Set myField = myPivot.Pivot-Fields("Number of dilutions")
myField.Orientation = xlColumnField


To refresh the pivot table take a look at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=80

BTW, there is data missing on your file!