Run Macro Data Export based on name of workbook on sheet1

[howard]

I have workbook names in sheet1. I would like to run the macro "Data Export" in each of these workbooks instead of macro below. If A new file/s files are added I need to amend the macro below each time



There could be between 15 -20 workbooks on which to run the macro

Sub Export_TB() 's()

Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.CutCopyMode = False

 Windows("M_BR1 ACCNTS(P).xlsm").Activate
    Application.Run "'M_BR1 ACCNTS(P).xlsm'!Data Export"
    ActiveWorkbook.Close
     Windows("M_BR1 ACCNTS(P).xlsm").Activate
     ActiveWorkbook.Close
    Windows("Open Pull Files.xlsm").Activate

 Windows("M_BR2 ACCNTS(P).xlsm").Activate
    Application.Run "'M_BR2 ACCNTS(P).xlsm'!Data Export"
    ActiveWorkbook.Close
     Windows("M_BR2 ACCNTS(P).xlsm").Activate
     ActiveWorkbook.Close
    Windows("Open Pull Files.xlsm").Activate
    
Windows("M_BR3 ACCNTS(P).xlsm").Activate
    Application.Run "'M_BR3 ACCNTS(P).xlsm'!Data Export"
    ActiveWorkbook.Close
     Windows("M_BR3 ACCNTS(P).xlsm").Activate
     ActiveWorkbook.Close
    Windows("Open Pull Files.xlsm").Activate
Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.CutCopyMode = False


End Sub

It would be appreciated if someone could assist me

 

[howard]

I have also posted on link below


https://www.excelforum.com/excel-pr...-workbook-on-sheet-workspace.html#post5071454

 

[mumps]

See if this works for you.

Sub Export_TB() 's()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .CutCopyMode = False
    End With
    Dim x As Long, lastrow As Long
    lastrow = Sheets("Workspace").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 1 To lastrow
        Windows("M_BR" & x & "ACCNTS(P).xlsm").Activate
        Application.Run "'M_BR" & x & "ACCNTS(P).xlsm'!Data Export"
        ActiveWorkbook.Close
        Windows("M_BR" & x & "ACCNTS(P).xlsm").Activate
        ActiveWorkbook.Close
        Windows("Open Pull Files.xlsm").Activate
    Next x
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .CutCopyMode = True
    End With
End Sub

 

[howard]

Thanks for the help

I get subscript out of range and the following code is highlighted

   Windows("M_BR" & x & "ACCNTS(P).xlsm").Activate

Please check & amend

 

[mumps]

Try:

Sub Export_TB() 's()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .CutCopyMode = False
    End With
    Dim x As Long, lastrow As Long
    lastrow = Sheets("Workspace").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 1 To lastrow
        Windows("M_BR" & x & " ACCNTS(P).xlsm").Activate
        Application.Run "'M_BR" & x & " ACCNTS(P).xlsm'!Data Export"
        ActiveWorkbook.Close
        Windows("M_BR" & x & " ACCNTS(P).xlsm").Activate
        ActiveWorkbook.Close
        Windows("Open Pull Files.xlsm").Activate
    Next x
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .CutCopyMode = True
    End With
End Sub

 

[howard]

Thanks for the help, code works perfectly

 

[mumps]

You are very welcome.