Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with Macro. -- User input for sheet name
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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






Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I would like to sort a work sheet based upon user input value,(dat John Bundy Excel Worksheet Functions 0 November 30th 06 04:18 PM
I would like to sort a work sheet based upon user input value,(dat A1CaddMan Excel Worksheet Functions 0 November 29th 06 03:53 PM
Macro for user to input 10 numbers Deedee New Users to Excel 4 October 21st 05 07:44 PM
Pausing a macro for user input Wayne[_5_] Excel Programming 1 October 24th 03 10:34 PM
Can a macro be suspended during user input? David DeArmond Excel Programming 2 August 1st 03 05:02 AM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement