Hi Shetty,
Do the 48 offices have internet connections? Servers or Co. LAN? Emailed the
workbooks?
You can use dataget external data to make a connection to each workbook,
add a worksheet, and pull the data from each office's workbook.

You have to know the name and location for each workbook.
You can do this by having each office save their workbook with a predefined
name and/or location.

Ex: "office1 10-23-03.xls" stored on shareddrive
\\servername\shareddrivename\path or folder (or the only file in that
folder)
Now you can create a loop using the servernames/sharenames stored in an
string array. The filename can be calculated within the loop. (you'll need
the sheetname also)

You can record a macro while doing dataget external data and the revise the
code by adding the loop.

A pain to setup but once it's working it will only take a few minutes to
complete.



--

John

johnf202 at hotmail dot com


"Shetty" wrote in message
om...
Can anyone help please?
Regards,
Shetty

(Shetty) wrote in message
. com...
I have tried the macro recorder but it works only for the specified
raws. If turn on the absolute referance, it messed up the things and
gives unexpected results.

Now to be more specific :

The central compiled sheet has got following columns.
Date, Office, mat size, run length, contractor, rate, amount, paid in
bill no . . . . . etc upto 32 columns.

Every day new raws of records are added by the area offices. I have to
compile the same and prepare the report every week.

What I have to to is to copy all the records (raws) of one office and
past it to a new sheet in the same workbook and rename the sheet with
the office name. These needs to be done for all the 48 offices. Hance
the total no of sheets in a file will be 48 plus one original central
sheet.

What I need is a macro or VB code that copies the raws of each office,
create a new sheet with the office name and paste the copied raws to
the same. This action cycle repeats for all the different 48 offices.
I hope this is clear.

I am also willing to share the workbook if required.

Regards,
Shetty