Want to add a user defined selection

[marcia2026]

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

 

[Gary''s Student]

Sub marcia()
Set ws1 = Sheets(Application.InputBox(prompt:="enter sheetname:", Type:=2))
End Sub

 

[Rick Rothstein \(MVP - VB\)]

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]

Thanks guys very much. Works great.