ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with Macro. -- User input for sheet name (https://www.excelbanter.com/excel-programming/349694-help-macro-user-input-sheet-name.html)

Michael A
Help with Macro. -- User input for sheet name
 
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
Help with Macro. -- User input for sheet name
 
Hi Michael,

Try changing:

Dim estuff As Worksheet

to

Dim estuff As String


---
Regards,
Norman


"Michael A" wrote in message
...
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



Michael A
Help with Macro. -- User input for sheet name
 
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


"Michael A" wrote in message
...
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
Help with Macro. -- User input for sheet name
 
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

Michael A
Help with Macro. -- User input for sheet name
 
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

Michael A
Help with Macro. -- User input for sheet name
 
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

Norman Jones
Help with Macro. -- User input for sheet name
 
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

Michael A
Help with Macro. -- User input for sheet name
 
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

Norman Jones
Help with Macro. -- User input for sheet name
 
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:

i = Application.CountIf(.Columns(2), nStuff).


---
Regards,
Norman



"Michael A" wrote in message
...
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
Help with Macro. -- User input for sheet name
 
Hey Norman,

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

"Norman Jones" wrote:

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:

i = Application.CountIf(.Columns(2), nStuff).


---
Regards,
Norman



"Michael A" wrote in message
...
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


All times are GMT +1. The time now is 12:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com