Thanks Carim!
Looks like it's working! I'll have to trim the cells due to the way the data is formatted and put in logic to close the loop but that is easy enough - truly appreciate the help!
Code
Sub JanFormat()
Dim WB2 As Workbook, WS2 As Worksheet, WS2_1 As Worksheet
Dim i As Integer, lastRow As Long, colNb As Long, MyColl As Collection, MyColl2 As Collection, myIterator As Variant, myIterator2 As Variant, FN As Integer, LN As Integer, FileN As Integer
Set WB2 = Workbooks("Working Updates_WORKING COPY.xlsx")
Set WS2_1 = WB2.Sheets("January Worked")
Set MyColl = New Collection
Set MyColl2 = New Collection
With WB2.Sheets("January Worked")
WS2_1.Activate
Range("A2").Activate
If ActiveCell = "" Then
Exit Sub
Else
End If
Range("A1").End(xlToRight).Offset(, 1).Select
ActiveCell.FormulaR1C1 = "Range 1 (Name-System)"
MyColl.Add "First Name"
MyColl2.Add "Range 1 (Name-System)"
lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 1 To 200
For Each myIterator In MyColl
If Cells(1, i) = myIterator Then
For j = 1 To 200
For Each myIterator2 In MyColl2
If Cells(1, j) = myIterator2 Then
Range("B2").End(xlToRight).Offset(, 1).Select
FN = i - j
LN = (i + 1) - j
FileN = (i + 2) - j
colNb = j
ActiveCell.Formula = "=CONCATENATE(RC[" & FN & "], "" "", RC[" & LN & "], ""-"", RC[" & FileN & "])"
Selection.Interior.Color = xlNone
Cells(2, colNb).Copy Destination:=Range(Cells(3, colNb), Cells(lastRow, colNb))
Range(Selection, Selection.End(xlDown)).Select
End If
Next
Next
End If
Next
Next
End With
End Sub
Display More