Auto Copy cells from one sheet to another workbook sheet
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 (each with specific formula in use) into a different sheet in a a different workbook.
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,
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.
Would the 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.