Auto Refresh Pivot tables

[shmattar]

hello everyone,
i do appreciate it if someone who love problem solving can help me in the below project challenge:

if i have multiple excel workbooks in a folder, and i need to take the details of item sold from these workbooks and update it in a new consolidated Workbook with Sheet name "Data" in a way i have all sold items and cost

information:
for sales:
sheet name SO
data required to capture must be starting from L23-R23, while first file should start from L22-R22 below is a snap from headers of data

Part No.​

Description​

QTY​

U.Price​

T.Price​

VAT%​

T.Price + VAT​

Pasting in data should be starting from A1-G1
the raws is not constant and i need to stop capture when Axx = Total Value without VAT


for cost:
sheet name PPlan
data required to capture must be starting from H15-S15, while first file should start from H-14-S14 below is a snap from headers of data

Part No.​

Item Type​

Scorecard​

Description​

Supplier​

Item Source​

QTY​

U.Price​

T.Cost​

Cost With Adjust.​

VAT%​

T.Cost + VAT​

pasting in data should start from H1-S1
the raws stop capture when Axx = Total Cost without VAT

finally i need to auto refresh the Pivot table with a sheet name "Report" in the same newly created consolidated workbook?

seems complicated mind set, but it will be nice to have

 

[highfly921]

Put this code below to refresh any tabs that contain pivot tables -

>>> as You've asked <<<
>>> use code - tags <<<

Sub Refresh_All_Pivot_Table_Caches()

'Refresh all pivot caches in the workbook.
'Pivot tables are automatically refreshed when cache is refreshed.


Dim pc As PivotCache

  'Refresh all pivot tables
  For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
  Next pc

End Sub