event procedure

Hi,

On 8/27 I posted a question regarding how to enter data once and have it go
to a noncorresponding cell on another sheet within the same workbook, moving
the data to another cell if the intended cell already held data. I received
in response a wonderful event procedure (thanks, Erin) that worked
beautifully in a "trial" workbook. When I applied the event procedure to my
actual workbook, however, it wouldn't work. Data entered into the input cell
didn't go anywhere. I thought one of you might be willing to help me
troubleshoot.

I've copied the event procedure below. I want data entered into sheet6,
cell C8 to go to sheet9, cell C22 (or if C22 already has data, to go to D22,
then E22, and so forth....). As I mentioned, the event procedure noted below
works when used on a new workbook. When it didn't work in my actual
workbook, I tried the following: made certain the cells did not contain
other commands (not sure if that is the right word), made certain I had the
correct sheet and cell numbers, made sure the sheets were unprotected and the
cells unlocked, and made certain the font color for C22 wasn't white. What
else should I consider?


I right-clicked on sheet6 tab and selected "View Code", then entered the
following:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

Dim NewCell As Range

If Target Range("C8") Then Exit Sub

Application.EnableEvents = False

Set NewCell = Sheets("Sheet9").Range("C22")

Do Until IsEmpty(NewCell)
Set NewCell = NewCell.Offset(0, 1)
Loop

NewCell.Value = Target.Value

Application.EnableEvents = True

End Sub


Then I resaved the file.

I greatly appreciate any and all thoughts, suggestions, ideas.
--
Rhonda Clark