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,
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.
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.
save to database
This can get quite involved... and I don't know your current VBA programming skills.
I'll start with showing you via VBA to save data to the end of existing data:
http://excelexperts.com/VBA-Tips-Find-The-Last-Cell
Let me know if this is on the right track, and we can build up the answer to this.
Save to database
OK, I think I got the idea of the find the last cell VBA and watched the video. Thats great, I think I am at the right place to find the answers and want to thank you.
I downloaded the "find-the-last-cell".xls and played with it while replaying the video. However, I keep getting a compile error: sytax error which highlights the Set MyCell = (rows.count, 3).End(x1Up).Offset(1, 0) line
I know I copied it exactly as shown.
xlup
it's:
MyCell = (65000, 3).End(xlUp).Offset(1, 0)
not:
MyCell = (65000, 3).End(x1Up).Offset(1, 0)
xlup
Thanxs !
If I wanted to set mycell to a different sheet (say sheet 2) on the same workbook, where would i reference sheet 2 on this line ?
Thanx in advance
MyCell =
MyCell = Sheets("Sheet2").(rows.count, 3).End(xlUp).Offset(1, 0)
Save To database
Is there any more on this please as I have the same problem. I am running excel 2002.
Thanks
Thank you for your response,
Thank you for your response, my VBA skill are very limited. I can look at and kind of understand but cannot create from scratch.
I will look at the link and let you know.
Again, I appreciate your time.
copy to database
ok, have a read of this:
http://excelexperts.com/Excel-Experts-Question-and-Answer-Forum
Add some screen shots of what you have, and what you want to end up with.