Absolute life saver! The macro worked. Thank you so much for your help Carim!
thank you for your initial answer. However, i was actually hoping to employ a loop where the value of L1 will change depending on where row it is in.
e.g. Active cell is in an24, so the value of L1 is based on the cell value of AV24. Then the loop runs again and the active cell becomes AN25 and the L1 should now be based on AV25
i'm having a hard time coding the loop because the L1 becomes fixed at AV24, instead of moving on to the next row.
I'm hoping someone can help me provide the missing piece to the code below.
I've tried to use a do-while loop but I can't make it work. I posted the part of the code that is working below.
Basically, I have to insert a formula in all the cells in column AN.
The macro i have changes a variable in the file path that's inside a VLOOKUP formula.
The variable is the data in Column AB. (it's assigned in the code as L1)
The formula in AN25 should use the value in AV25 as the value inside the file path.Code
Sub InsertFormulas() Dim x As String Dim L1 As String Dim ShtName As String L1 = ActiveCell.Offset(0, 8).Value ShtName = ActiveSheet.Name x = "C:\Users\carvajalk\Desktop\[" & L1 & ".xlsx]" & ShtName ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-35],'" & x & "'!R23C5:R29C40,36,0)" End Sub
thank you to anyone who can help.
I'm trying to split a file via macro using more than one criteria.
I used the code below (with named ranges) when I only used one criteria. (e.g. Split the files using data in column B)
Now, I need to split the files by splitting it first with the data in column B then split it again using the data in Column C before it saves into a separate workbook.
However, I'm not sure how to incorporate another criteria to the code below.
Any help is appreciated. Thank you!Code
Sub splitexcelfile() Dim cell As Range Dim curPath As String curPath = ActiveWorkbook.Path & "\" Application.ScreenUpdating = False Application.DisplayAlerts = False For Each cell In Range("lstSalesman") [valSalesman] = cell.Value Range("myList").AdvancedFilter Action:=xlFilterCopy, _ criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False Range(Range("Extract"), Range("Extract").End(xlDown)).Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents Next cell Application.ScreenUpdating = True Application.DisplayAlerts = True