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

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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 2021OTHER12578%JIndoor bike session, 60 mins.
#NUM!

Indoor Bike
Wed, 29 Sep 20211:00:0021.713.5812578%160J

Many thanks!
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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:
VBA Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
Are you data within Table, if yes, what is Table name?
Yes, if your last row should be defined at different column?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top