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

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


--
-SA


"Beth" wrote:

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

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.


"Beth" wrote:

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

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