Update The counts with criteria

[Abhijeet]

Hi
I have Data in Excel i want update count with criteria. In excel file 5 sheets from that Trust Numbers in Column C & Action Taken in Column F from that i want trust wise counts of Confirmation, Query Re Query & Error query.

Same Data Trust wise in every excel files & all files are saved in 1 folder.Same trust wise counts of Confirmation, Query Re Query & Error query. from each file i want to update .
Expected result i show in Attach file

 

[Abhijeet]

In One Folder trust wise different workbooks

 

[Abhijeet]

Can u please give me macro or any other solution for this problem

 

[Abhijeet]

I have this macro but i want all sheets need to pull & Pivot table as per my data i want so please tell me how to do this

 

[Abhijeet]

Can you please help me in this

 

[Luke M]

Here's how you could make it loop over the sheets. Since you started with this, I'm assuming that the sheet names are already known and you have the ODC query setup.

 

[Abhijeet]

Hi Luke M
Pivot fields fill manually i do not want that mention in macro then any type of data pull in pivot table & this macro gives error in this lineSet PT = .CreatePivotTable(TableDestination:=rng(6, 1))

 

[Luke M]

I'm not sure what you mean by manually.
The code line is showing where to put the PivotTable. You may need to move it over, perhaps to col Z? in that case, would do:

.CreatePivotTable(TableDestination:=rng(6, 26))

 

[Abhijeet]

If u look this video pivot table fields fill manually drag data in fields i want same

 

[Abhijeet]

Can u please help me in this

 

[Abhijeet]

Please tell me as per my data what changes in the code of this part.
With PT
With .PivotFields(1) 'Rep
.Orientation = xlRowField
.Position = 1
End With
.AddDataField .PivotFields(8), "Sales", xlSum 'Total
With .PivotFields(3) 'Region
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields(2) 'Date
.Orientation = xlColumnField
.Position = 1
.DataRange.Cells(1).Group _
Start:=True, _
End:=True, _
Periods:=Array(False, False, False, False, True, False, True)
End With
End With

 

[Abhijeet]

Can u please tell me changes in code as per my data

 

[Abhijeet]

Can u please tell me Luke M how to do this