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!

 

[Ironman]

No, data isn't within a table
I don't understand why it is defined at Column F - should it be the start of the row = Column A?

 

[maabadi]

Then if your 2nd Sheet name is "Indoor Bike" Not "Indoor Bike Session" Try this:

Sub Test()
Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
Lr2 = Sheets("Indoor Bike").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).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

End Sub

 

[Ironman]

Yes thanks maabadi, I realised that sheet was incorrectly named and renamed it at the start.

I have just run your amended code and still no data is copied.

 

[maabadi]

Are you have at Last row in Column I the Criteria: Indoor Bike Session
if yes, then try this and see what is result

Sub Test()
Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
Lr2 = Sheets("Indoor Bike").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).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]

Hi maabadi, just come back from a 3hr 20min run

Yes, Training Log Col I must have "Indoor Bike Session" as first words for the code to run.

I ran the amended code and the msg I got was "8778 , 359 * " (and no data copied)

 

[maabadi]

Then Do you want copy rows 8778? if Not, i think your question is wrong,
I think you want to find last row that Column I on it Start with "Indoor Bike Session"? Is this you want?

 

[Ironman]

Then Do you want copy rows 8778? if Not, i think your question is wrong,
I think you want to find last row that Column I on it Start with "Indoor Bike Session"? Is this you want?

Row 8778 is empty and is the last row that is filled (shaded green), as in my first post. The last row with data is row 8684 (although I am currently filling Row 8685 with data from the run I have just completed).

 

[Ironman]

Edit: my sincere apologies - I have just realised that Col G has a formula in it that I dragged down and wasn't visible. Am I able to keep this formula or does it interfere with the code? I would like to keep it if possible.

 

[maabadi]

Test this and upload Msgbox Result:

Sub Test2()
Dim Lr1 As Long, Lr2 As Long, Lr3 as Long, Lr4 As Long, Lr5 As Long, Lr6 as Long, Lr7 As Long, Lr8 As Long
Lr1 = Sheets("Training Log").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
Lr2 = Sheets("Training Log").Range("A" & Rows.Count).End(xlup).Row
Lr3 = Sheets("Training Log").Range("A2").End(xldown).Row
Lr4 = Sheets("Training Log").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Lr5 = Sheets("Indoor Bike").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row + 1
Lr6 = Sheets("Indoor Bike"). .Range("A" & Rows.Count).End(xlup).Row + 1
Lr7 = Sheets("Indoor Bike").Range("A2").End(xldown).Row + 1
Lr8 =  Sheets("Indoor Bike").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
MsgBox Lr1 & " , " & Lr2  & " , " & Lr3  & " , " & Lr4 & Vblf & Lr5 & " , " & Lr6  & " , " & Lr7  & " , " & Lr8
End Sub

 

[Ironman]

I deleted the formula in the unused rows before running your amended code.