Auto copy cell data from source sheet to another wrkbook sheet

[IVLUTA]

I have set up XLS sheet. Within the sheet are multiple columns and rows
which I would like to auto-copy the cell data into a different sheet in a a
different workbook. I have attempted Ron de Bruin's "What if the Database
sheet is in another workbook" script from his site. However, it did not work
and I do not know enough about de-bugging programing. I did follow the Notes
that he had and revised it to meet my specifics.

Basically what I am trying to do is (examples cell #'s):

When I fill in cells C5:C24, F5:F24, C29:C48, etc. in the specific "source"
sheet, I would like to auto populate (?) / copy the data in these cells into
a separate sheet in a separate workbook. However, where the "title" of each
cell in source sheet is vertical as rows, the corresponding "title" to the
cell data in dest sheet is set up in column layout. In addition, C5:C24
represents one row of data on the dest. sheet and F5:F24 would be another row
below on detination sheet.

The dest column sizes do not need to match as long as the data is transfered
automatically.

What I am trying to do is: I utilize the source sheet for formulas specific
to the collection of cells, I then copy/paste special into Word doc.
However, when I enter the information, I want to auto create a data base, so
I can in the future go into the data base and "pull in" information that I
have utilized on prior reports/xls workbook.

 

[Gary''s Student]

We can easily write an event macro to copy/paste data as it changes. However
it should be easier still to use simple linking formulas in the second
workbook to pick up data from the first.

 

[IVLUTA]

BTW- Excel 2003

Thank you for your quick response. The linking formula would not work
because, the source sheet would be different each time, different file path,
etc.

Basically, what I will have is a base templete xls sheet, which I would then
"save as" a new sheet and name, which I will input new data specific to a
clients word doc report.

Wouldnt the linking formula on dest sheet would loose the link once I save
as a new workbook.

Like you said, it would think it would have to be an event macro that is
specific to the sheet and gets copied along with everything else when I do a
save as from base templete.xls. Then, when I input data into cells it auto
copies/populates the cells to the "data base" workbook and sheet.