Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create a formula in one sheet that would read data from separate sheet automatically QD Excel Discussion (Misc queries) 0 December 8th 06 05:17 AM
linking pivot table data to a cell in other sheet sudip Excel Worksheet Functions 3 September 13th 06 02:34 PM
If data is on one sheet, linking to another nevergiveup New Users to Excel 2 June 27th 06 09:33 PM
can entered data in sheet 1 be automatically pasted in sheet 2 Adnan Jahangir Excel Discussion (Misc queries) 1 February 23rd 06 11:06 AM
Linking Data from 1 sheet to another sheet Warwick Everett Excel Worksheet Functions 5 December 21st 05 03:49 PM


All times are GMT +1. The time now is 12:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"