Recently I’ve got something to work on with several excel files.More than 150 excel files generated by a web based application have to be merged into one file and then create a summary from it.The resulting consolidated Pivot Table report can have the following fields in the Pivot Table Field List, which you can add to the Pivot Table report: Row, Column, and Value.In addition, the report can have up to four page filter fields, called Page1, Page2, Page3, and Page4. Open str File Name, Update Links:=False, Read Only:=True Set data WB = Active Workbook Range(str Copy Range). We open the data workbook by using the Application. Next we select the data that has been assigned to the copy range and copy to the clipboard. Inside our loop are the 4 variables which are assigned the 1) File name, 2) Copy Range, 3) Where To Copy and 4) Which Column contains the starting cell to paste data. Once we have our first data workbook open, we assign this to the data WB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.
The corresponding worksheets from each Excel file are named exactly the same as are the column headers.
This report can contain sales totals and averages, current inventory levels, and highest selling products for the whole enterprise.
To summarize and report results from separate worksheet ranges, you can consolidate data from each separate worksheet range into a Pivot Table report on a master worksheet.
Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together.
Do not include any total rows or total columns from the source data when you specify the data for the report.