Posts by angelring13

Absolute life saver! The macro worked. Thank you so much for your help Carim!

Hi 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.

Hi,

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.

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.

Hi!

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!

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

[ATTACH=CONFIG]74052[/ATTACH]