Open files with changing name

Question

Hi All,&nbsp;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 MacroIn line 7 the number (1) can go from 1to 15 everything else remains the same. I currently have 15 macros to run for each number, there must be an easier way?&nbsp;Sub obtaindata1()
'
' obtaindata1 Macro
'

'
    Windows("employeehistoryreport(1).csv").Activate
    Range("A2:AA600").Select
    Selection.Copy
    Windows("overtime workbook.xlsm").Activate
    ActiveSheet.Paste
    Range("E1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=AGGREGATE(2,3,R[2]C[-4]:R[599]C[-4])"
    Range("E2").Select
End Sub&nbsp;Thanks in advance&nbsp;John Cameron

hansvogelaar · Answer

John_cameron121&nbsp;
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 · Answer

HansVogelaar&nbsp;your a legend thank you so much 🙌🙌🙌 seems to be working well, will post if I have any other issues 😊😊

Forum Discussion

Open files with changing name

Share

Resources