Sub rahmanyam()
RunSEL ' filter raw data
PTable ' create pivot table
End Sub
Sub RunSEL()
Dim cn As Object, rs As Object, i%
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & _
";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
Set rs = cn.Execute("SELECT * FROM [sheet3$] WHERE Amount > 200")
i = 2
Do
Cells(i, 8) = rs(0)
Cells(i, 9) = rs(1)
Cells(i, 10) = rs(2)
Cells(i, 11) = rs(3)
i = i + 1
rs.Movenext
Loop Until rs.EOF
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub
Sub PTable()
Dim lr, sh As Worksheet, sdata$, dest$, pt As PivotTable
Set sh = Worksheets("sheet3")
lr = sh.Range("h" & Rows.Count).End(xlUp).Row
sdata = sh.Name & "!" & sh.[h1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
dest = sh.Name & "!" & sh.Cells(lr + 10, 8).Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.PivotCaches.Create(xlDatabase, sdata, 5).CreatePivotTable _
dest, "MyPivot", , xlPivotTableVersion15
Set pt = sh.PivotTables("MyPivot")
With pt.PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With
With pt.PivotFields("Employee")
.Orientation = xlRowField
.Position = 1
End With
pt.AddDataField pt.PivotFields("Amount"), "Sum", xlSum
pt.TableStyle2 = "PivotStyleMedium5"
End Sub