VBA Pull data from closed multiple workbooks into Master workbook/sheet

[breg523]

Hi everyone,

Not to savvy with VBA data gymnastics however I am more familiar with excel formulation. I have a bunch of workbooks in folder 'C:\Users\BCR\Desktop\TSA' i am only interested in one sheet 'Custom Award File' and only interested in columns A-I. The first row is row title and i want to append the data into a master sheet. the title of the workbook files are dynamic, based on the date and other random naming convention. My question is, is there a macro/vba that can copy data from all the closed workbooks in sheet 'Custom Award File' into a master workbook. the sheet name does not change in any of the files. Please let me know if you have any questions. There are about 10-30 files in the folder that get updated progressively throughout the month.

 

[mumps]

Place the macro in a regular module in your destination workbook and run it from there.

Sub ImportAccrual()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWB As Workbook, i As Long, cnt As Long
    Set desWS = ThisWorkbook.Sheets("Data")
    Dim LastRow As Long
    Const strPath As String = "C:\Users\A\Downloads\New\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With Sheets("Sheet1")
            With .Range("B5", .Range("B" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
                For i = 1 To .Areas.Count
                    cnt = .Areas.Item(i).Cells.Count
                    .Areas(i).Cells(1).Resize(cnt, 4).Copy
                    desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                    desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(cnt) = srcWB.Sheets("Sheet1").Range("B2")
                Next i
            End With
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[timo1031]

Place the macro in a regular module in your destination workbook and run it from there.

Sub ImportAccrual()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWB As Workbook, i As Long, cnt As Long
    Set desWS = ThisWorkbook.Sheets("Data")
    Dim LastRow As Long
    Const strPath As String = "C:\Users\A\Downloads\New\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With Sheets("Sheet1")
            With .Range("B5", .Range("B" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
                For i = 1 To .Areas.Count
                    cnt = .Areas.Item(i).Cells.Count
                    .Areas(i).Cells(1).Resize(cnt, 4).Copy
                    desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                    desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(cnt) = srcWB.Sheets("Sheet1").Range("B2")
                Next i
            End With
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

You are really genus!!! i worked perfect for my current data!! do you know how to do it for my future data where all rows have data from B-E but number of rows in different in different source workbook. Thanks a lot again!

 

[mumps]

You are very welcome. The macro should work for any combination of rows in your future data.

 

[timo1031]

You are very welcome. The macro should work for any combination of rows in your future data.

Thanks mumps. I think my excel has issue I cannot get desired result for future data. Even when I run it now for current data, it only copied all the dates to column A, the rest of data is not copied over...I need to figure out the root cause
Anyway, thanks again!

 

[mumps]

In order for the macro to work, the data in all the files must be organized in the same way. If you can post a screen shot (XL2BB) of the data for which it is not working, I can have a look.

 

[timo1031]

In order for the macro to work, the data in all the files must be organized in the same way. If you can post a screen shot (XL2BB) of the data for which it is not working, I can have a look.

But it is the same data - which worked earlier on but cannot now I left office already, will troubleshoot next week. Thanks mumps, have a nice weekend ahead.