Barbara
Here is a macro for you to try. I made up a small dummy file with some
data in it in order to develop the code I would need. This code works on my
file. I say this because it may not work on your file for a number of
reasons. For instance, this code searches each of the other 2 sheets for
the book title. The code is very dumb and it searches for the EXACT book
title that exists in Column B of the row and sheet in which the change was
made. A spelling difference or an extra space will cause a search failure.
For this reason I included an error trap in case the book title could not be
found in one of the other 2 sheets. In this instance, a message box will
pop up and tell you that title such-and-such could not be found in sheet
<sheet name>. The code will stop at that point. This may present a problem
for you because the code is looping through the other 2 sheets and may have
found the title in the first sheet in the loop and will have made the needed
changes in that first sheet, and then failed to find it in the second sheet,
and stopped then and not have made the changes in that second sheet. Think
about that.
If you think this may be a real problem with your data, I can write some
code for you to check that every title in every sheet can be found in every
other sheet. This would be some code that you would run just once or maybe
once in a while if you continuously add titles as I think you might.
Note that this macro is a workbook event macro and, as such, must be
placed in the workbook module of your file. In all versions of Excel short
of 2007 you can access that module by right-clicking the Excel icon that is
located immediately to the left of the word "File" in the menu that runs
across the top of your screen, and select View Code. Paste this macro into
that module. "X" out of the module to return to your sheet.
The code in this macro has some long lines that the newsgroup post may
wrap. This is a no-no because the code is not forgiving of line wrapping.
If you wish, send me an email and I'll send you the file I used for this and
it will have the code properly placed. My email address is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rColBSht As Range, Sht As Worksheet, TheRow As Long
Dim TheCol As Long, OldTitle As String, NewTitle As String
Dim rAllRng As Range
Set rAllRng = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rAllRng = rAllRng.Offset(, -1).Resize(, 5)
If Not Intersect(Target, rAllRng) Is Nothing Then
TheCol = Target.Column
If TheCol = 2 Then
NewTitle = Target.Value
Application.EnableEvents = False
Application.Undo
OldTitle = Target.Value
Target.Value = NewTitle
Application.EnableEvents = True
Else
OldTitle = Cells(Target.Row, 2).Value
End If
For Each Sht In ThisWorkbook.Worksheets
If Sht.Name <> Sh.Name Then
With Sht
Set rColBSht = .Range("B2", .Range("B" &
Rows.Count).End(xlUp))
If rColBSht.Find(What:=OldTitle,
LookAt:=xlWhole) Is Nothing Then
MsgBox "Book title '" & OldTitle & "'
cannot be found in sheet " & Sht.Name & "." & Chr(13) & _
"Note that some of the sheets may
have been updated.", 16, "Title Error"
Exit Sub
Else
TheRow = rColBSht.Find(What:=OldTitle,
LookAt:=xlWhole).Row
Application.EnableEvents = False
.Cells(TheRow, TheCol).Value =
Target.Value
Application.EnableEvents = True
End If
End With
End If
Next Sht
End If
End Sub