macros across multiple worksheets ...

[jer]

I am not sure where to post this. It was already posted
in the worksheet functions group. I have a workbook with
multiple sheets, a master sheet and "supporting"
sheets all of which are formatted similiarly The
supporting are subsets of the master broken down by type
of transactions based on customer type. I have used the
following to get the results I want on the mastersheet
(all this is still relatively new to me), however when I
attempt to do this for the other sheets I am getting all
zeros. Is there a way to have this run on all sheets

The ranges to sum are dynamic and not a static size.

Dim nonin As Range, nonout As Range, nbkcell As Range
Set nbkcell = ActiveSheet.Cells(65536, 5).End
(xlUp).Offset(1, 0)
Set nonin = Intersect(Range("e:e"),
ActiveSheet.UsedRange)
Set nonout = nonin.Offset(0, 3)
nonin.Name = "nonin"
nonout.Name = "nonout"

nbkcell.Offset(2, 0).Value = "=SUM(nonin)"
nbkcell.Offset(2, 3).Value = "=SUM(nonout)"
nbkcell.Offset(2, -1).Value = "=COUNT(nonin)"
nbkcell.Offset(4, 0).Value = "=SUM(nonin,nonout)"

TIA for any help/suggestions
jer

 

[Dave Peterson]

I'm not sure why you're getting all 0's, unless the last worksheet you run this
against has those formulas evaluate to 0.

When you use this line:
nonin.Name = "nonin"
This means that you've added a workbook level name called nonin. Each of your
formulas on the other sheets will use that last definition. Workbook level
names are global to the workbook.

If you create the name using the name of the sheet, you can reuse the same
"base" name as many times as you want:

Option Explicit
Sub testme()

Dim nonin As Range, nonout As Range, nbkcell As Range
Set nbkcell = ActiveSheet.Cells(65536, 5).End(xlUp).Offset(1, 0)
Set nonin = Intersect(Range("e:e"), ActiveSheet.UsedRange)
Set nonout = nonin.Offset(0, 3)
nonin.Name = "'" & ActiveSheet.Name & "'!nonin"
nonout.Name = "'" & ActiveSheet.Name & "'!nonout"

nbkcell.Offset(2, 0).Value = "=SUM(nonin)"
nbkcell.Offset(2, 3).Value = "=SUM(nonout)"
nbkcell.Offset(2, -1).Value = "=COUNT(nonin)"
nbkcell.Offset(4, 0).Value = "=SUM(nonin,nonout)"
End Sub

Since you're working with names, you may want to download a copy of Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.co.uk/mvp

(It really makes working with names easier.)