Macro to transfer data from multiple workbooks into separate sheets of master file

Hello,
I am new to macro but I know that this excel feature can help me a great deal.

The task is to copy and paste 20+ summary reports found in specific tabs named "Pivot" that exist in multiple workbooks located within the same folder located
in H:\Balance Sheet 2013

The macro will copy and paste pivot tables (summary reports) found in tabs named Pivot each into a separate worksheet within
a file called Balance Sheet Summary. The data to be transferred, from the summary reports to the Balance Sheet Summary workbook, always start on cell A3 but each summary report has different number of columns and different number of rows (some can be very large files).
Report users will still be able to drill down on the information residing behind these pivot tables.

Ideally the macro will name each new tab created with a combination of data found within cell B5 and cell C5.
Example B5="WE" and C5="201343" the result is "WE 4313".
Example B5="WA" and C5="201312" the result is "WA 1213".
But this is optional.

Is this something achievable or I am just kidding myself?

Any advice/help is much appreciated.
Many thanks
Sal

Hello Mike,
You raise a valid point.

Although the number of columns may differ among workbooks the aim is to have one summary pivot report copied and pasted in a new worksheet within the master report. So if there are
30 workbooks each containing a summary pivot report the macro will have created 30 worksheets within the master balance sheet summary. All summary pivots have the exact same format with reference to where it starts A3.

thought that if the macro, while looping through the workbooks, was to to highlight and copy the entire worksheet and then paste all this in the balance sheet summary report in a newly created worksheet the problem of having a different number of columns would not be an issue, because each pivot table (summary report) would have its own worksheet tab inthe balance sheet summary report.

I hope it makes sense.

Thanks again.
Sal