Combining tables into one using Macros - relative references

jguion

New Member
Joined
Aug 13, 2002
Messages
3
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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.
 

Forum statistics

Threads
1,143,640
Messages
5,719,984
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top