How do I display the result of a function on seperate worksheet e.

[Beth]

I have a cell that is counting text fields. Each month I want to capture the
count for that month and display it on a seperate worksheet.

Thanks for your help

 

[StumpedAgain]

Can you not just reference the first sheet in your second sheet? You said
you already have a cell that is doing the counting.

=*Sheet Name*!*Cell Name*
=Sheet1!B8

 

[JLatham]

Since the one doing the counting may change it's value (and so would any on
other sheets that refer to it), you may want to think of this:
Each month select the cell with the count in it and use Edit | Copy (or
[Ctrl]+[C]) and then go to where you want to record it and use
Edit | Paste Special with the "Values" option selected. That will copy the
value into the new cell so that it won't change when the original counting
cell does.

 

[JuanMarin]

You could try the following:

---------------------------------------------
Sub FixedDate()
Dim WS As Worksheet
Dim Y As Double
Dim m As Double
Dim Sname As String
Dim Celda As Range

Set Celda = Application.InputBox(prompt:="Current month's count",
Type:=8)
Y = Year(Date)
m = month(Date)

Sname = Str(m) & "-" & Str(Y)

For i = 1 To ActiveWorkbook.Sheets.Count
If ActiveWorkbook.Sheets(i).Name = Sname Then
MsgBox "there's already a worksheet for this month"
Exit Sub
End If
Next i

Set WS = Worksheets.Add
WS.Name = Sname
WS.Cells(1, 1) = Celda.Value

End Sub
----------------------------

it prompts you for the cell that's holding the count, then creates a
new worksheet named "month - year" and writes the count on cell A1. It
doesn't do much erro trapping but it should work. Hope this helps,

Juan M