Last line of code not running

[Ironman]

Hi

The below code runs almost perfectly

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
Lr3 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row

If Target.Column = 2 And Target.Value = "OTHER" Then
    Application.EnableEvents = False
Range("A" & Target.Row).Resize(, 6).Interior.Color = RGB(197, 217, 241)
Range("I" & Target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
Range("I" & Target.Row).Value = "Indoor bike session, 60 mins."
   
Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & lr).Value
Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"
Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"
Sheets("Indoor Bike").Range("J" & Lr2).Value = "Session "
   
Range("F" & Target.Row).Select 'move to this cell to input heart rate
MsgBox "Enter Average Heart Rate", vbInformation, "Indoor Bike Session Data"
End If

Sheets("Indoor Bike").Range("F" & Lr3).Value = Sheets("Training Log").Range("F" & lr).Value  'F = Heart Rate
' jump from F to H on the same row
If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then   'F = Heart Rate
Range("H" & lr).Select  'H = Session rating

Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value
   Application.EnableEvents = True
End If

The only part that isn't running i.e. it is not copying to Sheet 'Indoor Bike' is this line and I can't work out why

Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value

Help appreciated!

Thank you.

 

[Ironman]

P.S. Range("H" & lr).Select is for me to input data. After I have entered this, I expect the value to be copied to sheet 'Indoor Bike' but it isn't.

 

[RoryA]

It will only run that code if you change the value in column F for the last used row in column A. Also, your code disables events if you enter OTHER in column B and doesn't re-enable them.

 

[Ironman]

Thanks Rory, but I'm sorry, I don't understand what I need to do.

 

[RoryA]

First, is this code in the Indoor Bike sheet?

 

[Ironman]

No, the code is in Training Log sheet

 

[RoryA]

OK. Currently your code will run this line:

BikeSheet.Range("F" & Lr3).Value = Range("F" & lr).Value  'F = Heart Rate

if you change any cell on the Training Log sheet - is that really what you want?

 

[Ironman]

No it isn't. I only want the above code to run when the value in Col B of Training Log sheet = "OTHER", which it currently does OK. I just can't get the value (it's just a letter) in Col H of that sheet to copy to Col I of Indoor Bike sheet after I have input the letter.

 

[Ironman]

I've got it to work

Sheets("Indoor Bike").Range("F" & Lr3).Value = Sheets("Training Log").Range("F" & lr).Value  'F = Heart Rate
' jump from F to H on the same row
If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then   'F = Heart Rate
Range("H" & lr).Select  'H = Session rating
End If

If Target.Address(0, 0) = Range("H" & lr).Address(0, 0) Then
Range("I" & lr).Select  'H = Session rating
Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value
   Application.EnableEvents = True
End If