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]

Try:

Sub Test()
Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Range("F" & Rows.Count).End(xlUp).Row + 1
Lr2 = Sheets("Indoor Bike Session").Range("F" & Rows.Count).End(xlUp).Row + 1
'Lr2 = Sheets("Indoor Bike Session").Range("F2").End(xldown).Row + 1
If Sheets("Training Log").Range("I" & Lr1).Value <> "" Then
Sheets("Indoor Bike Session").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value
Sheets("Indoor Bike Session").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value
End If
End Sub

 

[Ironman]

Hi, many thanks for replying.

I ran the code and although it didn't error, I'm afraid nothing happened.

Just one other thing - would it be possible for you to also code copying Col A to Col A please?

Thanks again

 

[maabadi]

I'm afraid nothing happened.

Why?

also code copying Col A to Col A please

add this line before End if

Sheets("Indoor Bike Session").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value

 

[Ironman]

Many thanks for the additional line maabadi.

"Why"? I wish I knew. The code just didn't run i.e. no data was copied to the other sheet.

Edit - is this copying data from the last row of Training Log to the first empty row of Indoor Bike?

And what is the meaning of the commented out line please?

Thanks again

 

[maabadi]

Edit - is this copying data from the last row of Training Log to the first empty row of Indoor Bike?

this code find last row based column F data and if your last row is different, we should change it?

And why is the commented out line different to the line above please?

because you tell you want to add data after last filled row at 2nd sheet. this is second version for finding last row at 2nd sheet.

Do your column I at last row has text because you tell

sheet 'Training Log' contains the text 'Indoor Bike Session'

and
1.Do you want to 'Indoor Bike Session' to be partial text at column I
2. if yes, Do this text always come at the left of text or Not?

And sorry my fault change code to this:

Sub Test()
Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Range("F" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike Session").Range("F" & Rows.Count).End(xlUp).Row + 1
'Lr2 = Sheets("Indoor Bike Session").Range("F2").End(xldown).Row + 1
If Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 20)) = "Indoor Bike Session" Then
Sheets("Indoor Bike Session").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value
Sheets("Indoor Bike Session").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value
Sheets("Indoor Bike Session").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value
End If
End Sub

 

[Ironman]

Just to make clear:

Columns in last row in Training Log to transfer to first empty row of Indoor Bike sheet.

Training Log Column I MUST start with the words "Indoor Bike Session" for the code to run.

Thanks again.

 

[maabadi]

I Edit my post, please try new code.

 

[Ironman]

Thanks maabadi

I ran your amended code and still no data was copied from Col A, Col F or Col H of last row of Training Log sheet to Col A, Col F and Col I of the first empty row of Indoor Bike sheet.

1.Do you want to 'Indoor Bike Session' to be partial text at column I YES
2. if yes, Do this text always come at the left of text or Not? YES

if your last row is different, we should change it? Sorry, did you mean "Column"? Else, I don't understand

 

[maabadi]

Are you data within Table, if yes, what is Table name?
Yes, if your last row should be defined at different column?