VBA code to open files in a folder and copy from Sheet "Master" DK1:EA1 to DK2:EA2

[jskasango]

Hi, I am looking for assistance with a VBA code to open files in a folder and copy from Sheet "Master" DK1:EA1 to DK2:EA2, save and close.
I need this to run so that after it finishes I can then run a code that I was given by maabadi.

 

[John_w]

Try this macro, changing the folder path and wildcard file name spec as required.

Public Sub Copy_Range_In_Workbooks()

    Dim matchWorkbooks As String
    Dim folderPath As String
    Dim wbFileName As String
    Dim wb As Workbook
    
    'Folder path and wildcard workbook files containing range to be copied
    
    matchWorkbooks = "C:\folder\path\*.xls*"                                'CHANGE THIS
    
    Application.ScreenUpdating = False
            
    folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
    wbFileName = Dir(matchWorkbooks)
    While wbFileName <> vbNullString
        Set wb = Workbooks.Open(folderPath & wbFileName)
        wb.Worksheets("Master").Range("DK1:EA1").Copy wb.Worksheets("Master").Range("DK2:EA2")
        wb.Close savechanges:=True
        wbFileName = Dir
    Wend
    
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

 

[jskasango]

Try this macro, changing the folder path and wildcard file name spec as required.

Public Sub Copy_Range_In_Workbooks()

    Dim matchWorkbooks As String
    Dim folderPath As String
    Dim wbFileName As String
    Dim wb As Workbook
   
    'Folder path and wildcard workbook files containing range to be copied
   
    matchWorkbooks = "C:\folder\path\*.xls*"                                'CHANGE THIS
   
    Application.ScreenUpdating = False
           
    folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
    wbFileName = Dir(matchWorkbooks)
    While wbFileName <> vbNullString
        Set wb = Workbooks.Open(folderPath & wbFileName)
        wb.Worksheets("Master").Range("DK1:EA1").Copy wb.Worksheets("Master").Range("DK2:EA2")
        wb.Close savechanges:=True
        wbFileName = Dir
    Wend
   
    Application.ScreenUpdating = True
   
    MsgBox "Finished"
   
End Sub

Maybe it would be less cumbersome if I could be prompted for the folder, instead of having to edit the code each time I want to tell it to work on a folder?

 

[John_w]

prompted for the folder

Search for Application.FileDialog(msoFileDialogFolderPicker)

 

[jskasango]

Thanks!