Prompt the user for an existing sheet

[Shairal]

I will preface this with I have very little experience with VBA.

I have a little piece of code that copies data on a worksheet based on the
Range.AutoFilter method.

I now want to prompt my end user to enter the tab name of the worksheet that
he wants to copy the data to. I have found that I can set the name of the
sheet:
Set DestSheet = Sheets("SparkPlugs")
But I want the user to select the correct worksheet, instead of creating a
macro for each spreadsheet available.

I am able to prompt my user for the sheet name
DestSheet = InputBox("Enter the name of the sheet you want to copy the data
to.", _
"Enter Sheet Name")
But I don’t know how to make the sheet equal the name provided in the
message box.

Thanks in advance for your assistance.

 

[Jacob Skaria]

Try the below. I would suggest to have a userform with combobox listing all
sheets so that the user can select and you can avoid validation for valid
sheetnames...

Dim strSheet As String, DestSheet As Variant
strSheet = InputBox("Enter the name of the sheet")
On Error Resume Next
Set DestSheet = Sheets(strSheet)
If DestSheet Is Nothing Then _
MsgBox "Invalid Sheet entered": Exit Sub

If this post helps click Yes

 

[Bernie Deitrick]

Shairal,

You will need to include error checking:

Sub TryNow()
Dim DestSheet As Worksheet
Dim myName As String

On Error GoTo BadName

GetName:
myName = InputBox("Enter the name of the sheet you want to copy the data to.", _
"Enter Sheet Name")
myName = Worksheets(myName).Name
Set DestSheet = Sheets(myName)
MsgBox "Sheet """ & myName & """ does exist, so I will select it now."
DestSheet.Select
Exit Sub

BadName:
MsgBox "The sheet """ & myName & """ does not exist!"
Resume GetName
End Sub



HTH,
Bernie
MS Excel MVP

 

[arjen van der wal]

Another way to do this is to create a userform with a listbox. Thsi way
the user doesn't have to type out a name (in case some of the sheet names are
long).

The following procedures can be placed in the code module for the
userform.

First to populate the listbox (I've used the name lstSheets for the
listbox here):

Private Sub UserForm_Initialize()

Dim SheetCount As Integer
SheetCount = ThisWorkbook.Sheets.Count

Dim i As Integer

For i = 1 To SheetCount
lstSheets.AddItem (Sheets(i).Name)
Next i

End Sub

Then a second procedure to select the sheet:

Private Sub CommandButton1_Click()

Dim MySheet As String
MySheet = CStr(lstSheets.Value)
MsgBox (MySheet)

If MySheet = "" Then
MsgBox Prompt:="You must choose a sheet", Title:="No choice made"
Else
ThisWorkbook.Sheets(MySheet).Activate
End If

End Sub

Also note that for this to work, on the properties window for the
listbox, the MuultiSelect property must be set to: 0-fmMultiSelectSingle

 

[Shairal]

Thanks everyone for your responses!! I have learned a lot.

Jacob, yours was the easiest for me to understand and be able to put right
to work. Thank!
Arjen, I love the idea of the list, but tried as I may, I could not get it
to work. I'm sure it is due to my lack of understanding of VBA. I'm going
to continue to play with it and see if I can get it to work.

Thanks again!!
Shairal