Filter, Count Filtered results, Copy the Filtered result to Next sheet using VBA.

[99Ahmad99]

Hi

I have a workbook consisting of two sheets. The sheets have headers I don't need to touch.
First sheets, information started at row 12 to row 2450. what I need is a VBA code that first filters the data located in columns (I) based on cell value located in columns(S), (AD) and (AO). in those columns, there is a cell value which is a letter(like B, C, ), so each row that belongs to columns (I) has a letter in columns(S) (AD) and AO contains letters (B,C,D). count them and paste it into a table located into next sheet named sheet2 into cell G45 yo G73.

this is what I tried to do with a help of friend

:
Sub PivotTable_Sum()

' Get the worksheets
Dim shRead As Worksheet, shWrite As Worksheet
Set shRead = ThisWorkbook.Worksheets("Transactions")
Set shWrite = ThisWorkbook.Worksheets("Report")

' Get the range
Dim rg As Range
Set rg = shRead.Range("A1").CurrentRegion

' Clear any existing pivot tables
Dim piv As PivotTable
For Each piv In shWrite.PivotTables
piv.TableRange2.Clear
Next piv

' Clear the data in output worksheet
shWrite.Cells.ClearContents

' Create the cache
Dim ptCache As PivotCache
Set ptCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase _
, SourceData:=rg _
, Version:=xlPivotTableVersion15)

' Create the table
Dim ptTable As PivotTable
Set ptTable = shWrite.PivotTables.Add( _
PivotCache:=ptCache _
, TableDestination:=shWrite.Range("A1"))

' Set the name and style
ptTable.name = "pvItems"
ptTable.TableStyle2 = "PivotStyleDark14"

' Set the fields
Dim ptField As PivotField
With ptTable

' Set the rows fields
.PivotFields("Item").Orientation = xlRowField

' Set the data(value) fields
.PivotFields("Volume").Orientation = xlDataField
.PivotFields("Sales").Orientation = xlDataField

End With

End Sub
Sub PivotTable_Sum()


Dim shRead As Worksheet, shWrite As Worksheet
Set shRead = ThisWorkbook.Worksheets("Transactions")
Set shWrite = ThisWorkbook.Worksheets("Report")

' Dim rg As Range
Set rg = shRead.Range("xxx").CurrentRegion

' Clear any existing pivot tables
Dim piv As PivotTable
For Each piv In shWrite.PivotTables
piv.TableRange2.Clear
Next piv

' Clear the data in output worksheet
shWrite.Cells.ClearContents


Dim ptCache As PivotCache
Set ptCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase _
, SourceData:=rg _
, Version:=xlPivotTableVersion15)


Dim ptTable As PivotTable
Set ptTable = shWrite.PivotTables.Add( _
PivotCache:=ptCache _
, TableDestination:=shWrite.Range("A1"))


ptTable.name = "pvItems"
ptTable.TableStyle2 = "PivotStyleDark14"


Dim ptField As PivotField
With ptTable

' Set the rows fields
.PivotFields("Item").Orientation = xlRowField

' Set the data(value) fields
.PivotFields("Volume").Orientation = xlDataField
.PivotFields("Sales").Orientation = xlDataField

End With

End Sub

 

[99Ahmad99]

Gents.
Any Clue?