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

Try the following code:

Option Explicit

Sub AAA()

Dim S As String
Dim WS As Worksheet
Dim WSArr() As String
Dim Ndx As Long

S = InputBox("Enter Text")
If S = "" Then
Exit Sub
End If

For Each WS In ThisWorkbook.Worksheets
If Right(WS.Name, Len(S)) = S Then
Ndx = Ndx + 1
ReDim Preserve WSArr(1 To Ndx)
WSArr(Ndx) = WS.Name
End If
Next WS
Worksheets(WSArr).Copy
ActiveWorkbook.SaveAs S & ".xls"

End Sub

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


"Simon Lloyd"
wrote
in message
...

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

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