First, please excuse my lack of knowledge of proper Excel and programing
terminology,

I have set up an 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.

Basically what I am trying to do is (example 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 data in cells, I then copy & paste special, link to
excel, into a Word doc. However, when I enter the information in excel, in
addition, 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.

I have come to the conclusion that a linking formula on the destination
workgroup/sheet 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.

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

I 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.

PS-when I save the xls workbook as a "save as" it becomes a .xlsm with an
exclamation point. Why is this.

Thank you in advance for any input.

Hi,

You could write linking formulas? No code needed in that case.

for example

='[Quick Parts.xlsm]Formulas'!$A$37

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"IVLUTA" wrote:

First, please excuse my lack of knowledge of proper Excel and programing
terminology,

I have set up an 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.

Basically what I am trying to do is (example 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 data in cells, I then copy & paste special, link to
excel, into a Word doc. However, when I enter the information in excel, in
addition, 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.

I have come to the conclusion that a linking formula on the destination
workgroup/sheet 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.

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

I 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.

PS-when I save the xls workbook as a "save as" it becomes a .xlsm with an
exclamation point. Why is this.

Thank you in advance for any input.

Shane, Thanks for the quick response,
Could I write a linking formula in the source sheet and have it stick each
time I "save as" a new copy and is it possible to have lets say cells C5:C24
linked to one row and cells F5:F24 be copied to the next row and so on, etc.
I dont think I can write a linking formula in the dest. sheet as the
location/name of the source sheet would be different each time.

Is this correct, again thank you.


"Shane Devenshire" wrote:

Hi,

You could write linking formulas? No code needed in that case.

for example

='[Quick Parts.xlsm]Formulas'!$A$37

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"IVLUTA" wrote:

First, please excuse my lack of knowledge of proper Excel and programing
terminology,

I have set up an 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.

Basically what I am trying to do is (example 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 data in cells, I then copy & paste special, link to
excel, into a Word doc. However, when I enter the information in excel, in
addition, 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.

I have come to the conclusion that a linking formula on the destination
workgroup/sheet 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.

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

I 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.

PS-when I save the xls workbook as a "save as" it becomes a .xlsm with an
exclamation point. Why is this.

Thank you in advance for any input.