Open files with changing name

[John_cameron121]

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 15 everything else remains the same. I currently have 15 macros to run for each number, there must be an easier way?

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

Thanks in advance

John Cameron

[#2]

@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

[#3]

@Hans Vogelaar your a legend thank you so much seems to be working well, will post if I have any other issues