All,
I am trying to write a macro within a central model (let's call this the 'aggregator model') to open a series of spreadsheets (one at a time), then copy data and paste as hardcoded values into the central model.
The main difficulty I can see, is identifying the area where the data should be pasted in the aggregator model, as it must be on a separate/specific worksheet for each underlying spreadsheet.
To get set up, I have:
- saved all the underlying spreadsheets into a single folder
- set the copy area and paste areas as the exact same size
- applied a named range ("CopyArea") to the area to be copied from each underlying spreadsheet
- applied a named range ("PasteArea") to the area to be pasted to on each worksheet within the aggregator model (note: these named ranges are attached to the worksheets, not the workbook)
On a separate control worksheet in the aggregator model, I have also created a list of all the external underlying models (saved in column A) and matched the desired worksheet that it should be pasted to (column B). So this provides a list of all the external workbooks to be used, and shows which worksheet in the aggregator model that they relate to.
I have to import data from c.100 spreadsheets, and I am sure there must be a much more efficient way of doing this that manually opening each then copying and pasting!
In case it makes a difference, I use Excel 2016.
Really appreciate your help!
Thanks
I am trying to write a macro within a central model (let's call this the 'aggregator model') to open a series of spreadsheets (one at a time), then copy data and paste as hardcoded values into the central model.
The main difficulty I can see, is identifying the area where the data should be pasted in the aggregator model, as it must be on a separate/specific worksheet for each underlying spreadsheet.
To get set up, I have:
- saved all the underlying spreadsheets into a single folder
- set the copy area and paste areas as the exact same size
- applied a named range ("CopyArea") to the area to be copied from each underlying spreadsheet
- applied a named range ("PasteArea") to the area to be pasted to on each worksheet within the aggregator model (note: these named ranges are attached to the worksheets, not the workbook)
On a separate control worksheet in the aggregator model, I have also created a list of all the external underlying models (saved in column A) and matched the desired worksheet that it should be pasted to (column B). So this provides a list of all the external workbooks to be used, and shows which worksheet in the aggregator model that they relate to.
I have to import data from c.100 spreadsheets, and I am sure there must be a much more efficient way of doing this that manually opening each then copying and pasting!
In case it makes a difference, I use Excel 2016.
Really appreciate your help!
Thanks
