Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking data from a new sheet (semi-)automatically
I have read in help about inserting a worksheet from a template (does this
only work if it is the default, in which case how do I get a blank sheet when I want one?) After I have manually named the sheet, I want, either automatically or with a key-assigned macro, to create links to cells A1, B1 & C1 (where client name, work in progress value, forward order value are stored) in a sheet which is compiling this info for all current sheets (ie, customers). Im sure this is a very common application of Excel. Maybe someone can point me to the exercise already described elsewhere. Can I do this with a simple macro? Would I have to be very experienced with VBA to do it automatically? Incidentally, it is not ideal that the info be in cells A1:C1. As items are added to each sheet the €˜running totals cells move further down the sheet, but I am doing a classic €˜getaround of linking A1:C1 to the totals cells I am trying to improve my knowledge/use of Excel and at the same time make things easier for my colleagues. Ive browsed this site and am finding lots of information but always have further, more specific questions. Its so good to see experienced people being generous with their knowledge and saving us lots of research time. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking data from a new sheet (semi-)automatically
You could copy an existing worksheet in that workbook to create a new workbook.
That worksheet could be a template (blank except for the important stuff you want on it--or it could be an existing sheet that has values close to the one you are creating. After you do the rename, keep that sheet active and run this macro: Option Explicit Sub Testme() dim CustLog as worksheet Dim NextRow as long set custlog = worksheets("Customers") '<-- change to what it's called with custlog nextrow = .cells(.rows.count,"A").end(xlup).row + 1 end with with custlog.cells(nextrow,"A") .numberformat = "mm/dd/yyyy hh:mm:ss" .value = now end with with activesheet custlog.cells(nextrow,"b").value = .range("a1").value custlog.cells(nextrow,"c").value = .range("b1").value custlog.cells(nextrow,"d").value = .range("C1").value end with end sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Alison KS wrote: I have read in help about inserting a worksheet from a template (does this only work if it is the default, in which case how do I get a blank sheet when I want one?) After I have manually named the sheet, I want, either automatically or with a key-assigned macro, to create links to cells A1, B1 & C1 (where client name, work in progress value, forward order value are stored) in a sheet which is compiling this info for all current sheets (ie, customers). Im sure this is a very common application of Excel. Maybe someone can point me to the exercise already described elsewhere. Can I do this with a simple macro? Would I have to be very experienced with VBA to do it automatically? Incidentally, it is not ideal that the info be in cells A1:C1. As items are added to each sheet the €˜running totals cells move further down the sheet, but I am doing a classic €˜getaround of linking A1:C1 to the totals cells I am trying to improve my knowledge/use of Excel and at the same time make things easier for my colleagues. Ive browsed this site and am finding lots of information but always have further, more specific questions. Its so good to see experienced people being generous with their knowledge and saving us lots of research time. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a formula in one sheet that would read data from separate sheet automatically | Excel Discussion (Misc queries) | |||
linking pivot table data to a cell in other sheet | Excel Worksheet Functions | |||
If data is on one sheet, linking to another | New Users to Excel | |||
can entered data in sheet 1 be automatically pasted in sheet 2 | Excel Discussion (Misc queries) | |||
Linking Data from 1 sheet to another sheet | Excel Worksheet Functions |