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.

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