Copy To New Sheet

[caveman.savant]

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

(example sheet at http://spreadsheets.google.com/ccc?key=pgpAUsMDN5oUSDoqTgI6sRA
)


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.

 

[JLGWhiz]

Your description leaves a lot of holes.

'continue through each column and row of the 1st sheet until the range is
complete.'

Which range? Column B? Row 2? UsedRange? Describe the range. If it is no
all numeric, there is no need to subtract strings.

The assumption would be the the seventh cell below another should be
subtracted on each iteration.

How do you want the results entered into the new worksheet? Continue down
column B? Continue across Row 2?

 

[caveman.savant]

My bad.
The sheet has a set rows and columns with numbers or false as values.
the last row and column has no values in them.
This would be B2:G3 in this example.

B9:G10 also have values in them, but just numbers.

I want to subtract B9 from B2, copy that result to a new sheet at B2.
The new sheet would look just as B2:G3 in the orginal sheet. Then C9
minus C2, continuing to do the same until G9 - G2. Next go back to B10
and subtract from B3.

Sometimes the number of columns and rows are more that the example
I've show here. Maybe sometime 5 row & 5 Columns. always with a blanks
cell following or in this case in H3 the total of the cells.


Sorry for not being clear. I'm just a Caveman after all.

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[caveman.savant]

Works really good.
On the 1st row.
Can we get it to go back to the 1st column and down a row, until the
rows with values stop?