Macro to copy range from mutilple spreadsheet and paste into central file

[Dav S]

Hi

i require a macro to copy a range from multiple workbooks that are saved in the same directory. The range will also be consistent V198:AG198 and the sheet name will also be the same called Budget. I then require for this range to be pasted into a central file in a list format starting in cell A3

Help much appreciated. Thanks

 

[Logit]

.
This macro copies the range from each workbook located in a folder named "New Folder". Either change the name of the folder in the macro or place all of your workbooks TO BE COPIED in a folder called New Folder.
The 'new folder' should be in the same path as the Master Workbook that will have the ranges pasted into.

Ex: Master Workbook located at : C:\Users\My\Desktop\MasterWorkbook.xlsm

All workbooks to be copied located at : C:\Users\My\Desktop\New Folder\*.xls*

Option Explicit


Sub consolidatePres2()
Dim fPath As String, fName As String, wb As Workbook, sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
fPath = ThisWorkbook.Path


Application.ScreenUpdating = False


If Right(fPath, 1) <> "\" Then fPath = fPath & "\New Folder\"   '<-- change folder name as required
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
                With wb.Sheets(1)
                    sh.Cells(Rows.Count, 1).End(xlUp)(3) = wb.Name
                    .Range("V198:AG198").Copy sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 1)
                End With
                wb.Close False
        End If
        fName = Dir
    Loop
Application.ScreenUpdating = True


Beep
MsgBox "All workbooks in folder have been processed."


End Sub

 

[Dav S]

Thank you very much for your reply

currently the master workbook is stored in another location. Can this code be altered to account for this

 

[Logit]

.
Yes .. just edit the paths.