Thanks Norman,

It dosn't seem to be working though. I am not getting a returned value,
however if I do a countif on the actual sheet then i do get a value
returned... any thoughts?



"Norman Jones" wrote:

Hi Michael,

There is no need to insert a formula and subsequently delete it. It is also
rarely necessary to activate sheets.

Try, instead:

'<<=============
Sub MAM()
Dim nStuff As String
Dim ThisSheet As String
Dim i As Long

ThisSheet = ActiveSheet.Name

nStuff = InputBox _
("What date is this for? I.E 1/1/2006,1/5/2006 etc.")

Application.ScreenUpdating = False
Workbooks.Open Filename:="\\server2\mam.xls"

With Worksheets("Jan 06")
'Now working in the MAM Sheet
i = Application.CountIf(.Columns(2), nStuff)
End With

Workbooks("January Stats.xls"). _
Worksheets(ThisSheet).Range("B44").Value = i

ActiveWorkbook.Close False
Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman


"Michael A" wrote in message
...
I made a mistake.. I ment delete the cell contents, not the sheet.

"Michael A" wrote:

Hi Norman, that worked. Thank you.

If your still watching this thread, maybe you could help with another
one..
its similar, but I want to add a forumla to a worksheet, get the value,
then
delete the sheet. Please see below. Thank you very much for your help.


Sub MAM()

Dim nStuff As String
Dim ThisSheet As String

ThisSheet = ActiveSheet.Name



nStuff = InputBox("What date is this for? I.E 1/1/2006,1/5/2006 etc.")

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\server2\mam.xls"

Worksheets("Jan 06").Activate

' Now working in the MAM Sheet

Range("Q5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-15],""nstuff"")"


Workbooks("January Stats.xls").Worksheets(ThisSheet).Range("B44") =
Workbooks("Mam.xls").Worksheets("Jan 06").Range("Q5").Value

Range("Q5").Select
Selection.ClearContents

ActiveWorkbook.Close False
Application.ScreenUpdating = True

End Sub


"Norman Jones" wrote:

Hi Michael,

Additionally, change:

estuff.Activate

to Sheets(estuff).Activate


---
Regards,
Norman


"Michael A" wrote in message
...
Hi Norman,

I have tried that as well. I get the error

"invalid qualifer" on line : estuff.Activate

Im trying to activate that sheet.. im stuck. Any other suggestions?
And
thanks for the response.

- Mike

"Norman Jones" wrote:

Hi Michael,

Try changing:

Dim estuff As Worksheet

to

Dim estuff As String


---
Regards,
Norman