Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to add a user defined selection
Hi, I am using a copy of Ron de Bruin's "Copy to Worksheets" macro. I would
like to enter a place for the user to define which worksheet to copy. The number of sheets each month will vary and names will vary. Otherwise, works great. This is the current line of code: 'Name of the sheet with your data Set ws1 = Sheets("Sheet1") '<<< Change |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to add a user defined selection
Sub marcia()
Set ws1 = Sheets(Application.InputBox(prompt:="enter sheetname:", Type:=2)) End Sub -- Gary''s Student - gsnu200797 "marcia2026" wrote: Hi, I am using a copy of Ron de Bruin's "Copy to Worksheets" macro. I would like to enter a place for the user to define which worksheet to copy. The number of sheets each month will vary and names will vary. Otherwise, works great. This is the current line of code: 'Name of the sheet with your data Set ws1 = Sheets("Sheet1") '<<< Change |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to add a user defined selection
I would think replacing the indicated line with this would do what you
want... '*************** START OF REPLACEMENT *************** Do strInput = InputBox("Some prompt for input") If Len(strInput) = 0 Then If StrPtr(strInput) = 0 Then ' User clicked Cancel Button, exit subroutine Exit Sub End If Else On Error Resume Next Set ws1 = Sheets(strInput) If Err.Number = 0 Then Exit Do Else MsgBox """" & strInput & """ is not a valid Sheet Name!" End If End If Loop '*************** END OF REPLACEMENT *************** ' ' The subroutine code continues here ' Don't forget to Dim the strInput variable as String. Rick "marcia2026" wrote in message ... Hi, I am using a copy of Ron de Bruin's "Copy to Worksheets" macro. I would like to enter a place for the user to define which worksheet to copy. The number of sheets each month will vary and names will vary. Otherwise, works great. This is the current line of code: 'Name of the sheet with your data Set ws1 = Sheets("Sheet1") '<<< Change |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to add a user defined selection
Thanks guys very much. Works great.
"marcia2026" wrote: Hi, I am using a copy of Ron de Bruin's "Copy to Worksheets" macro. I would like to enter a place for the user to define which worksheet to copy. The number of sheets each month will vary and names will vary. Otherwise, works great. This is the current line of code: 'Name of the sheet with your data Set ws1 = Sheets("Sheet1") '<<< Change |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
Workspace faux user-defined type not defined | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
Word.Document - user defined type not defined | Excel Programming | |||
How do I convert a user defined selection into an array? | Excel Programming |