Integrate module into an existing worksheet_change event

[Ironman]

Hi

I have the following code, very kindly supplied by maabadi in a module

Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
If UCase(Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 19))) = "INDOOR BIKE SESSION" Then
Sheets("Indoor Bike").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value
Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value
Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).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 "
End If
End Sub

I also have the following code in a worksheet_change event, kindly supplied by NoSparks

Dim NextRow As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

If Target.Address(0, 0) = Range("A" & Rows.Count).End(xlUp).Address(0, 0) Then
Application.EnableEvents = False
    Range("C" & Target.Row).Select 'move to this cell to start inputting data

    MsgBox "Enter distance", vbInformation, "Indoor Bike Session Data"
End If

' jump from C to H on that same row
If Target.Address(0, 0) = Range("C" & lr).Address(0, 0) Then
    Range("H" & lr).Select
    MsgBox "Enter Average Watts", vbInformation, "Indoor Bike Session Data"
End If

If Target.Address(0, 0) = Range("H" & lr).Address(0, 0) Then
    Range("J" & lr).Select
Application.EnableEvents = True
End If

I just need the above sets of code to be combined as a worksheet_change event.

The code needs to start in Col C, not A as written above, because A is now copied as part of the first set of code. It then needs to jump from Col C to Col H and then Col J.

Many thanks!

 

[Ironman]

Many thanks!

I've tried both:

.Range("C" & .Rows.Count).End(xlUp).Select

Goes to the row above the newly added row so I tried

.Range("C" & .Rows.Count).Enc(xlUp).Offset(1).Select

And it returned

Run-time error 438
Object doesn't support this property or method

This is the code above your lines in Post #10:

If Target.Column = 8 And Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then
Range("H" & Target.Row).Validation.Delete 'added 31.10.2021 - clears validation input info, no longer needed
    Lr1 = Target.Row
    If UCase(Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 19))) = "INDOOR BIKE SESSION" Then
        Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("Indoor Bike").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value  'ave heart rate
        Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value  'session rating
        Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value  'date
        Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"  'session length
        Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"  'resistence level

Thanks again!

 

[NoSparks]

I see that I spelled End wrong, sorry 'bout that.

 

[Ironman]

Aha, no worries, I missed that too!

Thanks ever so much, it's just perfect now!

I presume this is no longer needed and can be deleted now?

Private Sub Worksheet_Activate()

    lr = Range("A" & Rows.Count).End(xlUp).Row
    If Range("C" & lr) = "" Then
        Range("C" & lr).Select 'move to this cell to start inputting data
        MsgBox "Enter Distance", vbInformation, "Indoor Bike Session Data"
    
End If

End Sub