Lookup or Match

[Guest]

Not sure how to go about this, but what i'm trying to do is that from an
output worksheet i want to ensure the names column matches the lookup
worksheet names. I then want the names in the output worksheet replaced with
the entry from the lookup worksheet.

OUTPUT(before)
Mr Fred Harry Bloggs
Mr Richard John Smith

LOOKUP
Fred Bloggs
Richard Smith

OUTPUT(after)
Fred Bloggs
Richard Smith

Thanks

 

[Guest]

I am not clear as to what you have/want....what I think you have is 2
workbooks, one with sheet names "Fred Bloggs" and "Richard Smith" and
another workbook with sheet names "Mr Fred Harry Bloggs" and "Mr Richard John
Smith" and you want the second workbooks sheet names to match the first. Is
this correct?

If this is the case, is it always the same pattern (ie. remove the prefix
and the middle name)?

Mike

 

[Guest]

Worksheet 1 = Output
Worksheet 2 = Lookup
I want to ensure that the names in worksheet 1 are the same as worksheet 2.
Is there any code to do this

 

[Guest]

Insert the following code into a module in the template workbook (the book
with the sheet titles you want). I put a measure in the code to handle
multiple sheets for the same name (ie. Mr Fred Bloggs and Mr Fred Harry
Bloggs). If you don't want it use the second code below.

Mike

Option Explicit
Sub UpdateSheetNames()
Dim This As Workbook, That As Workbook
Dim shthis As Worksheet, shthat As Worksheet, ws As Worksheet
Dim first As String, last As String
Dim i As Integer
Set This = ThisWorkbook
Set That = Workbooks.Open("P:\Excel Help\MatchSheets2.xls")
For Each shthis In This.Worksheets
i = 1
first = Left(shthis.Name, InStr(1, shthis.Name, " ") - 1)
last = Right(shthis.Name, Len(shthis.Name) - _
InStr(1, shthis.Name, " "))
For Each shthat In That.Worksheets
If InStr(1, shthat.Name, first) <> 0 And _
InStr(1, shthat.Name, last) <> 0 And _
InStr(1, shthat.Name, first) < InStr(1, shthat.Name, last) Then
On Error Resume Next
MsgBox (first & " " & last)
Set ws = That.Sheets(first & " " & last)
On Error GoTo 0
If ws Is Nothing Then
shthat.Name = shthis.Name
Else
shthat.Name = shthis.Name & i
i = i + 1
Set ws = Nothing
End If
End If
Next shthat
Next shthis
End Sub

'****** END OF CODE ******


'**** SECOND CODE ****
Option Explicit
Sub UpdateSheetNames()
Dim This As Workbook, That As Workbook
Dim shthis As Worksheet, shthat As Worksheet, ws As Worksheet
Dim first As String, last As String
Dim i As Integer
Set This = ThisWorkbook
Set That = Workbooks.Open("P:\Excel Help\MatchSheets2.xls")
For Each shthis In This.Worksheets
i = 1
first = Left(shthis.Name, InStr(1, shthis.Name, " ") - 1)
last = Right(shthis.Name, Len(shthis.Name) - _
InStr(1, shthis.Name, " "))
For Each shthat In That.Worksheets
If InStr(1, shthat.Name, first) <> 0 And _
InStr(1, shthat.Name, last) <> 0 And _
InStr(1, shthat.Name, first) < InStr(1, shthat.Name, last) Then
shthat.Name = shthis.Name
End If
End If
Next shthat
Next shthis
End Sub

'****** END OF CODE ******