Help with Macro. -- User input for sheet name

[Guest]

Hello, could someone please help me with this. I cant seem to have it
activate the sheet based on the user input. Here is what I have. I can't
quite figure out how to get this to work right. Any help would be greatly
appreciated. Thanks!


Sub CopyStuff()



Dim estuff As Worksheet
Dim ThisSheet As String

ThisSheet = ActiveSheet.Name



estuff = InputBox("What date is this for? I.E 1,2,3 etc.")

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\network1\January.xls"

estuff.Activate

' Now working JanC

Workbooks("January Stats.xls").Worksheets(ThisSheet).Range("B37") =
Workbooks("January.xls").Worksheets(estuff).Range("U2").Value
Workbooks("January Stats.xls").Worksheets(ThisSheet).Range("A41") =
Workbooks("January.xls").Worksheets(estuff).Range("V2").Value
Workbooks("January Stats.xls").Worksheets(ThisSheet).Range("B41") =
Workbooks("January.xls").Worksheets(estuff).Range("W2").Value

ActiveWorkbook.Close False
Application.ScreenUpdating = True
End Sub

 

[Norman Jones]

Hi Michael,

Try changing:

Dim estuff As Worksheet

to

Dim estuff As String

 

[Guest]

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]

Hi Michael,

Additionally, change:

to Sheets(estuff).Activate

 

[Guest]

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

 

[Guest]

I made a mistake.. I ment delete the cell contents, not the sheet.

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]

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

I made a mistake.. I ment delete the cell contents, not the sheet.

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

Hi Michael,

Additionally, change:

estuff.Activate

to Sheets(estuff).Activate


---
Regards,
Norman


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

:

Hi Michael,

Try changing:

Dim estuff As Worksheet

to

Dim estuff As String

 

[Guest]

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?

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

I made a mistake.. I ment delete the cell contents, not the sheet.

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


:

Hi Michael,

Additionally, change:

estuff.Activate

to Sheets(estuff).Activate


---
Regards,
Norman


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

:

Hi Michael,

Try changing:

Dim estuff As Worksheet

to

Dim estuff As String

 

[Norman Jones]

Hi Mike,

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?

It works in my test book. Did you perhaps drop the period (full stop) that
prepends Columns(2) in the following line:

 

[Guest]

Hey Norman,

It was an error on my part. Thanks for you help!