Combining tables into one using Macros - relative references

[jguion]

I have seven workbooks which contain exactly the same number of columns but a different number of rows. I am looking to copy and paste the data from each into one long workbook which contains all of the data. I can then use this to produce pivot tables.

I am using Excel 2002 and cannot see the relative reference button that normally appears. I need to use relative reference because the number of rows of data will change every week. Thus without relative references the macro will fail. Does anyone know how to activate this in excel 2002? Or can you suggest an alernative solution.
thanks,
James

 

[DonkeyOte]

you could work out the following

a) which is the next blank line in the consolidated sheet
b) which is the last line on the sheet of data that you wish to copy to the consolidated sheet

By working these out you will be able to paste the copied data under the last bit of copied data in the consolidated sheet.

There are various ways of working this out...

one simple way is a loop

Data = range("a1:a10")
crow = 1

for each i in data

if i <> "" then crow = crow + 1

next i

the other way around this...if you are using a pivot table - how large is the data ever going to be from each sheet? You could paste into this area and leave blanks if not full. Setting up the pivot table can include the blank lines - then just unselect blank in your pivot table menus.