Just to clean up the Dim statements. By the way, this goes in the public
module1.
Also, I would suggest that you substitute the actual sheet name for the
first Set statement: Set sh = Sheets("use actual sheet name"), so that if
you are not on that sheet when you run the macro, it will still work
correctly. The new sheet automatically becomes the active sheet when it is
created.

Sub tract()
Dim sh As Worksheet, ns As Worksheet, lc As Long
Dim n As Long
Set sh = ActiveSheet
Worksheets.Add After:=Sheets(Sheets.Count)
Set ns = ActiveSheet
lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
n = 2
For i = 2 To lc
ns.Cells(2, n) = sh.Cells(2, i) - sh.Cells(9, i)
n = n + 1
Next
End Sub







"JLGWhiz" wrote in message
...
I should have checked you link first. I assumed that you want the results
on
the new sheet to be in the same order as the data on the source sheet.

Sub tract()
Dim sh As Worksheet, ns As Worksheet, lc As Long
Dim lcns As Long
Set sh = ActiveSheet
Worksheets.Add After:=Sheets(Sheets.Count)
Set ns = ActiveSheet
lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
n = 2
For i = 2 To lc
ns.Cells(2, n) = sh.Cells(2, i) - sh.Cells(9, i)
n = n + 1
Next
End Sub



"caveman.savant" wrote:

I would like to create a new worksheet and create values based on an
active sheet

(example sheet at
http://spreadsheets.google.com/ccc?k...5oUSDoqTgI6sRA
)


The macro would look at the active sheet and subtract the value of B9
from B2 and put the result into a new sheet at B2. It would continue
through each column and row of the 1st sheet until the range is
complete.