Great! very nearly there. I have applied the same code to run this for 3 seperate lists at teh same time (3 lists are all of different lengths)

So the code I am using is ....



Sub Concatenate()

Dim LRow As Long

LRow = Sheets("SquadLists Import").Cells(Rows.Count, 1).End(xlUp).Row

'Podium List

With Sheets("SquadLists")
.Range("A2:A" & LRow).ClearContents
With .Range("A2:A" & LRow)
.Formula = "='Squadlists Import'!A2&"" ""&'Squadlists Import'!B2"

.Value = .Value
End With
End With


'PP List

With Sheets("SquadLists")
.Range("B2:B" & LRow).ClearContents
With .Range("B2:B" & LRow)
.Formula = "='Squadlists Import'!C2&"" ""&'Squadlists Import'!D2"

.Value = .Value
End With
End With


'Historical List

With Sheets("SquadLists")
.Range("C2:C" & LRow).ClearContents
With .Range("C2:C" & LRow)
.Formula = "='Squadlists Import'!E2&"" ""&'Squadlists Import'!F2"

.Value = .Value
End With
End With

End Sub




However the range to copy gets set by this line meaning that if the second list is longer than the first not everything gets copied over. I have tried to include this within each WITH statement, but it dosent allow multiple Dim LRow statements in the same process.


Dim LRow As Long

LRow = Sheets("SquadLists Import").Cells(Rows.Count, 1).End(xlUp).Row



Thanks so much for your help!