Can a Macro extract cell references from a Column and paste them into a Row.

Please see the attached (simplified!) example.

Hundreds of Reports are filed Vertically on a worksheet.

However, I need to do a summary table on a different worksheet.

The idea is that whenever a new report is created (in the correct place under all the others) the summary table will automatically be up-dated with data from the new report.

What I would like to do is write an Excel Macro that fill in all the Cell References in the Summary Table. (Because the data is copied from a spaced out column to a compact row I don't think I can achieve this by Copying & Pasting).

Thank you for any offers of help; if you just help me get started I will do all the donkey work myself!

Hi,

You may find the attached approach which uses a Pivot Table more useful.Itg avoids the use of functions or macros apart from one macro that automatically refreshes the Pivot Table when you select the sheet.

You just need to make sure that on the Reports sheet the formulae in the helper columns D:G are copied down. Columns E:G are the data source for the Pivot Table and there's a dynamic range name "Data" that automatically adjusts as you copy the formulae down which is used as the source for the PT.

Or something like this.
Based on your example with the "Merge and Center" cells
It deletes the "Summary" sheet if it is there and makes a new "Summary" sheet to start clean.

Please Login or Register  to view this content.