How to code/run a macro?

[David]

I've just started learning Excel 2000.
I have to create a workbook for projected sales for 2004.
One dimension will be each day in the fiscal year, the
other will be restaurant units.
I would have liked the columns to be the days but ran into
the 255 max column limitation, so I made the rows the days
and the columns the units.
The workbook needs to be a template that can be loaded by
different divisions that run different restaurants. The
days will be preset in the template but the units will be
filled in by the division (thus I don't know how many
columns there will be).

I will need a macro that does the following:
Reads all the data for the worksheet and creates a new
worksheet.
The new worksheet will contain the same rows and columns
but instead of the sales figures will contain a percentage
such that for each unit the percentages add up to 100.
This figure will be the percentage of yearly sales
attributed to each day.
How do I code this macro and how would I run it?

In particular, how do I refer to rows and columns, how do
I total each units sales to figure the percentage
(remember I dont know how many units there will be), and
how do I load all this into the new worksheet?

Any help appreciated to get me started.
- David

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
Dim dblTotal as Double
Activesheet.copy After:=ActiveSheet
set rng = Range("A1").CurrentRegion
' Assume first column and first row are headers
set rng = rng.offset(1,1).Resize(rng.rows.count=1,rng.columns.count-1)
for each col in rng.columns
dblTotal = Application.Sum(col)
if dblTotal <> 0 then
for each cell in col.Cells
cell.Value = cell.Value/dblTotal
cell.Numberformat = "0.0%"
Next
Next

 

[David]

ok, I figured out the 'end if' is missing; you also had
an '=' instead of an '-' in the second set statement.
It now works fine. Thanks!
- David

 

[David]

How do I give the new worksheet created a name?
- David