Forum Discussion

Copper Contributor

Jan 30, 2023

Open files with changing name

Hi All, I have a macro that operates fine. Problem is every now and then the file name changes by 1 digit. Can anyone please help me? Below is the Macro In line 7 the number (1) can go from 1to...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

MVP

Jan 30, 2023

John_cameron121

Try this:

Sub obtaindata1()
    Dim wbk As Workbook
    For Each wbk In Workbooks
        If InStr(1, wbk.Name, "employeehistoryreport", vbTextCompare) Then
            wbk.Worksheets(1).Range("A2:AA600").Copy
            Windows("overtime workbook.xlsm").Activate
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Range("E1").FormulaR1C1 = "=AGGREGATE(2,3,R[2]C[-4]:R[599]C[-4])"
            Exit For
        End If
    Next wbk
End Sub

John_cameron121
Copper Contributor
Jan 30, 2023
HansVogelaar your a legend thank you so much 🙌🙌🙌 seems to be working well, will post if I have any other issues 😊😊