Copy contents of Col F in last row of Sheet 1 to same location Sheet 2 when text entered in Sheet 1 Col i

[Ironman]

Hi

I need the values in Col F Col G and Col H in the last filled row of sheet 'Training Log' to be copied to the first empty row in Col F Col G and Col I (NOT H) in sheet 'Indoor Bike' only if/when Col I of the last filled row of sheet 'Training Log' contains the text 'Indoor Bike Session'.

For clarity, the tables below both show the value 125 in Col F, 78% in Col G, the value J in Col H and I and the text 'Indoor Bike Session...' in Col I of Training Log.

Training Log

Wed, 29 Sep 2021	OTHER				125	78%	J	Indoor bike session, 60 mins.
						#NUM!

Indoor Bike

Wed, 29 Sep 2021	1:00:00	21.7	13.5	8	125	78%	160	J

Many thanks!

 

[maabadi]

without deleting formula test it, I want know which one find your correct last row

 

[Ironman]

Ok, I put the formulas in Col G back in.

 

[maabadi]

Then this should be working:

Sub Test()
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 Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 20)) = "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
End If
MsgBox Lr1 & " , " & Lr2 & Vblf & Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 20)) & "*"
End Sub

 

[Ironman]

I ran it and got this message (correct rows but no data copied)

 

[maabadi]

I find the Problem Try this:

        Sub Test()
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 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
End If
MsgBox Lr1 & " , " & Lr2 & Vblf & Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 20)) & "*"
End Sub

 

[maabadi]

Or Use this:

Sub Test()
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
End If
MsgBox Lr1 & " , " & Lr2 & Vblf & Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 20)) & "*"
End Sub

 

[NoSparks]

Another approach may be to look at the date columns for last and write rows and use InStr to check for Indoor Bike Session

Sub TryThis()
    Dim LastRow As Long, WriteRow As Long
    Dim src As Worksheet, dest As Worksheet
    
Set src = Sheets("Training Log")
Set dest = Sheets("Indoor Bike")

WriteRow = dest.Range("A" & Rows.Count).End(xlUp).Row + 1

With src
    LastRow = src.Range("A" & Rows.Count).End(xlUp).Row
    If InStr("Indoor Bike Session", .Range("I" & LastRow).Value) <> 0 Then
        dest.Range("A" & WriteRow).Value = .Range("A" & LastRow).Value
        dest.Range("F" & WriteRow).Value = .Range("F" & LastRow).Value
        dest.Range("G" & WriteRow).Value = .Range("G" & LastRow).Value
        dest.Range("I" & WriteRow).Value = .Range("H" & LastRow).Value
    End If
End With

End Sub

 

[Ironman]

Yes! That works, thank you so much maabadi for persevering with me!

@NoSparks - Hi Nolan, I'm really pleased you're watching - I need to amend the code you very kindly did for me just slightly if you'd be OK to do that please?

I have added part of your code to the above and this is what I have now

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 just need to combine the above with the below code you did for me to run as a worksheet_change event (I have deleted the Heart Rate Data rows as it has now been copied, thanks to maabadi).
The code needs to start in Col C, not A, because A has also been copied, as above. It then needs to jump from Col C to Col H and then Col J.

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

 

[NoSparks]

Sorry Paul and maabadi,

when I posted no response had yet been marked as the answer and when I saw that maabadi's post was marked as the answer I tried to delete my post but it was past the 10 minute time limit.
My suggestion actually doesn't account for maybe Indoor not having a capital I, so wouldn't consistently work.

As for what you're now asking,
are you saying that the code maabadi supplied should be integrated as part of the "Indoor Bike" Worksheet_Change event ?
Maybe you should be asking as a new question.

 

[Ironman]

are you saying that the code maabadi supplied should be integrated as part of the "Indoor Bike" Worksheet_Change event ?

Yes please Nolan - sure, I'll do that now.