Importing data from multiple workbooks into a single spreadsheet

**kflotlin** · 05-07-2012, 07:17 PM

Hey all,

I'm attempting to consolidate a ton of data from multiple workbooks into one spreadsheet. Unfortunately it's been a while since I've done any programming and it was never with VBA... I think I can struggle through data selection/copy process but I'm not sure how to access multiple workbooks.

The data I want to copy from the workbooks is always in the same cells, which helps. If anyone could give me some quick pointers on how to start this macro up and access other workbooks I'd really appreciate it.

Secondary problem: One of the sections of data I'm copying is a column of data points which would correspond with the y-axis of a graph. The x-axis values are a couple columns over, but I'd like to only copy the y- data to save space in the other workbook. The problem is that the range of the x- data will occasionally extend to larger values without changing the rest of the values. I would need the macro to "see" which x- value the y- data begins at and place it accordingly. That was extremely hard to understand so I'll try illustrating:

Data Source 1:

x	y
10	1
9	3
8	2
7	3
6	4

Data Source 2:

x	y
9	2
8	1
7	3
6	4
5	5

What I'd Like To Construct:

x	y1	y2
10	1
9	3	2
8	2	1
7	3	3
6	4	4
5		5

Hopefully that makes it a little more obvious. I'm assuming I'll just have to have the macro look to see what value the data begins collection at and then have it match up to the proper row in the destination table.

I'll be scanning some intro VBA material I saw posted in the stickied topic as well, hopefully that will help. Thanks for any help on this!

**kflotlin** · 05-08-2012, 11:48 AM

I've been working through some tutorials and testing out recording my own macros, but I'm still struggling with this. Any help appreciated

(stealth bump)

**JieJenn** · 05-08-2012, 11:51 AM

You talking about consolidate files into a master file?

**arlu1201** · 05-08-2012, 11:52 AM

Regarding your 1st problem, i have a couple of questions -

1. Where are these files stored? All in 1 folder?

2. Which is the range to be copied?

3. Does your primary problem have anything to do with the secondary problem?

Maybe you can attach a sample file of 1 or 2 of the workbooks that need to be consolidated and also the master file where the consolidation should happen.

To Attach a File:

1. Click on Go Advanced.
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.