Dave's solution is preferable to mine since he uses ReDim
Preserve only once.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dave Peterson" wrote in message
...
Yep.

Something like this might get you started:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim wCtr As Long
Dim myStr As String
Dim myNames() As String

myStr = InputBox(Prompt:="enter the suffix")
If Trim(myStr) = "" Then
Exit Sub 'user hit cancel
End If

ReDim myNames(1 To Worksheets.Count)

wCtr = 0
For Each wks In Worksheets
If LCase(wks.Name) Like "*" & LCase(myStr) Then
wCtr = wCtr + 1
myNames(wCtr) = wks.Name
End If
Next wks

If wCtr = 0 Then
MsgBox "No sheets matched!"
Else
'remove any unused elements
ReDim Preserve myNames(1 To wCtr)
Worksheets(myNames).Copy
With ActiveWorkbook
.SaveAs Filename:=myStr & ".xls",
FileFormat:=xlWorkbookNormal
.Close savechanges:=False
End With
End If

End Sub

If you wanted that suffix to be separated with a space, you may
want:
If LCase(wks.Name) Like "* " & LCase(myStr) Then
instead of:
If LCase(wks.Name) Like "*" & LCase(myStr) Then

Simon Lloyd wrote:

Hi all,

Is there a way of selecting all w/s using VBA that have the
same last
name typed in an input box? lets say the w/s is called "Bob
Goes Here"
and another "Bob in here" and another "Bob over Here" etc is
it
possible to type in an input box "Here" and have it select all
sheets
with the last name "Here" and move them to a new workbook
Named by the
name in the input box so in this case "Here.xls"?

Hope you can Help,

Regards,Simon

--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread:
http://www.excelforum.com/showthread...hreadid=547267

--

Dave Peterson