I've got the below set of code which I'll be calling into a main portion of code and will be the same for each month of the year. The worksheets for each month are separate and the data placement within each sheet can vary - in order to look for the correct data I've come up with the below code which looks at specific headers ('First Name' and 'Range 1 (Name-System)') and from there it will use the cell references within the concatenate formula that would ultimately be pasted below the 'Range 1..." header.
Where I'm a bit stuck is with the autofill function. My macro steps through everything just fine and is placing the initial formula in the correct cell but in order to autofill it down to the end of the data it requires a range - since each sheet has a different number of columns I can't just indicate U2:U or T2:2 etc. and need this to be variable based on where my current active cell is (where that concatenate formula is currently being inserted correctly).
I'm self taught for VBA coding so I'm sure there are areas my code can be cleaned/simplified as well so any help is appreciated!
Sub JanFormat() Dim WB2 As Workbook, WS2 As Worksheet, WS2_1 As Worksheet Dim i As Integer, lastRow 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 ActiveCell.Formula = "=CONCATENATE(RC[" & FN & "], "" "", RC[" & LN & "], ""-"", RC[" & FileN & "])" Selection.Interior.Color = xlNone Selection.AutoFill Destination:=Range(Variable & Range("E" & Rows.Count).End(xlUp).Row) Range(Selection, Selection.End(xlDown)).Select End If Next Next End If Next Next End With End Sub