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


"Bernie Deitrick" wrote:

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


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