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