Macro for multiple workbook files and various tabs

**blondewithbrains** · 08-05-2011, 01:03 PM

Hey guys,

I am new to this forum and am desparately in need of some help!

I just recently started a job and they asked me to build out a macro for some excel spreadsheets but I do not even know where to begin!

Here is the situation:
I have five different excel workbooks. The first four each have data that is updated from a database each month. The fifth is a workbook with four (4) different tabs in it. Each tab pulls data from one of the other four workbooks respectively and then outputs it the compiled workbook. The information from the first four workbooks always starts in the same place (A16) but the number of columns and rows differs, so I need to be able to copy from A16 to the last column and last row and then output that information into the respective tab in the 5th workbook starting in A16 again.

Is there anyway to link all five of these workbooks so that a single macro can be run on command and update the 5th workbook? If so, how? What would the code have to look like for this?

PLEASE HELPPPPPP!!!!

**jaslake** · 08-05-2011, 05:07 PM

Hi blondewithbrains

Welcome to the Forum!!

Couple of questions...
1. How many tabs do the first 4 workbooks have? What are they named? Is only the 1st tab populated with data? Is it ALWAYS the first tab? If so, what's the Code Name?

To get the Code Name go into VBA (Alt + F11)...you'll see a split screen (if not do Control + R). On the left side you'll see VBA Project(Book1.xlsm) or some such...the name will be your workbook name. If VBA Project(Book1.xlsm) isn't expanded, expand it (click on the + sign). Under that you'll see "Microsoft Excel Objects" and under that each of your worksheets is listed. For example, you may see this
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)

The underlined portion is the Sheet Code Name. That's what I'm interested in (for each of the first 4 workbooks).

2. The 5th workbook has 4 tabs...what are their names?

Correlate the 4 tabs in workbook 5 with the first 4 workbooks:

Book 5 Tab 1 (insert name) receives data from Book ? Sheet Code Name ?
Book 5 Tab 2 (insert name) receives data from Book ? Sheet Code Name ?
Book 5 Tab 3 (insert name) receives data from Book ? Sheet Code Name ?
Book 5 Tab 4 (insert name) receives data from Book ? Sheet Code Name ?

Are the requisite Header Rows already in Book 5?

3. It would be helpful if you posted an actual copy of Book 5 (only with Header Rows...data isn't necessary). This'll give me a snapshot of what the first 4 Books look like.

4. Of course, if you're able, a sample file for each of the first 4 books will reduce development time

.