Macros/VBA: Stacking data from multiple sources in excel

Question

Hello Community.

Please can any of you clever people help me? I am very new to macros and vba and am having difficulty in trying to find a vba macro to solve my problem.

I have a workbook that I am building to serve as a "reader file" in order to consolidate several sources of similarly titled data, (from which I can generate summary pivots and graphs etc). 

I have a number of other workbooks that contain the source data in excel, (this data is maintained and regularly updated). I would like to read the data contained in a range of rows (e.g. A2:A5) in a particular column (all of which have the same header name) within each of these separate source data workbooks.

Then stack the contents one on top of the other in my reader file (N.B. I don't want to create a new worksheet to contain the new data). 

I'll try to explain below:

Source Data Workbook 1

Sheet 1 Column B

"Current Price" (Header Name)

33.00

5.00

66.00

7.00

Source Data Workbook 2:

Sheet 1 Column K

"Current Price" (Header Name)

100.00

11.00

12.00

131.00

Desired Output:

Reader File Sheet 1 Column A

"Current Price" (Header Name)

33.00

5.00

66.00

7.00

100.00

11.00

12.00

131.00

I would like the data to appear in the order that they appear in the source data file columns, (so not sorted into any order).

Please can someone help?!!

Thank you guys!

Answer 1

There is a couple of ways of doing this and it depends on your requirements.

Could provide code that will open a dialog similar to the open command in Excel and you can select one file at a time or select multiple files from which to extract the data and copy to the single output workbook.

Alternatively can simply loop through all the files in a folder and copy the required data and paste to the Output workbook. This method works well if all of the source data files can be in the same folder. If there are other files in the folder that are NOT to be included, if the required files have a common string in the name (which will not be in the files to exclude) then this can be handled also but it is best if the source files are all in the same folder.

Where do you want the VBA code? 1. In the destination workbook, 2.  In the Output workbook or 3. In a stand alone workbook from which the code is run and it will open the Output workbook and copy the data to it.

How do we identify the Source required sheet in the Source workbook? Will it always be the first sheet or will it always have the same name and if so what is the name?

I am assuming that for each workbook it will be necessary to locate the column header "Current Price".

Following paragraph added after initial posting:

If you run the code again at a later date, do you still want to use the same reader file? If so, do you want to append the new data to bottom of the previous data or do you want to delete the previous data?

I'll await your reply.