I need to have a macro that will take look at a cell ("A1") then divide
that by the number of worksheets. So if I have 200 in cell A1 on
worksheet 3 it would be 200/3. Then on worksheet 4 it would be 200/4
and so on.

First enter this small UDF:

Function whereami()
s = Application.Caller.Parent.Name
For i = 1 To Sheets.Count
If Sheets(i).Name = s Then
whereami = i
Exit Function
End If
Next
End Function


It will return the number of the sheet on which it is entered. So if cell
A1 on any sheet contains 200, then:

=A1/whereami()

will give you your desired result.
--
Gary's Student


"newguy" wrote:

I need to have a macro that will take look at a cell ("A1") then divide
that by the number of worksheets. So if I have 200 in cell A1 on
worksheet 3 it would be 200/3. Then on worksheet 4 it would be 200/4
and so on.

This one works with the active sheet and is run from the standard VBA module.

Sub divWs()
MyInt = ActiveSheet.Index
MyVal = Range("$A$1") / MyInt
MsgBox "Result is " & MyVal
End Sub

"newguy" wrote:

I need to have a macro that will take look at a cell ("A1") then divide
that by the number of worksheets. So if I have 200 in cell A1 on
worksheet 3 it would be 200/3. Then on worksheet 4 it would be 200/4
and so on.