Write a VBA Code and VLOOKUP Values from multiple excel files into a master sheet

[Charles Wanda]

Hi,

Grateful if anyone can help with this.

I have a set of production data which is reported on a daily basis (Image A: Report For January 1, 2022) i.e different excel Worksheets that represent production data for a specific day(date). There exist a specific folder on my desktop where all the reports are saved.
However, to consolidate these production data for the different days into a single master table/excel in a specific order as seen on Image B (Master Excel Sheet Table)), I have to manually enter the data. I realized this method is prone to mistakes.

Given that the daily reporting format for everyday remains the same, what VBA code can I use to automate the process?


Thanks in advance and I look forward to your responses.

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of one of your files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
In image A, you have data for only one date. Will this always be the case or can there be data for more than one date? If more than one date, do you want the Master to contain all the dates? If only one date, do you want a Master for each file in your folder. Will the Master be in the same workbook as the production data sheet? Please clarify in detail. What is the full path to the folder containing the files?

 

[Charles Wanda]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of one of your files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
In image A, you have data for only one date. Will this always be the case or can there be data for more than one date? If more than one date, do you want the Master to contain all the dates? If only one date, do you want a Master for each file in your folder. Will the Master be in the same workbook as the production data sheet? Please clarify in detail. What is the full path to the folder containing the files?

Hi Mumps,

Thanks for your response. To respond to your questions, there are different worksheets for the production data. This is reported on a daily basis. For the master, it is a separate worksheet from the production data. The table in the master worksheet runs from the 1st of January 2022 to the 21st of December 2022 as seen using the link attached. However, it could extend beyond 2022.

The link below can be used to access the sample files.

test

Shared with Dropbox

www.dropbox.com

The full file path for the folder containing the production data is: C:\Users\charles.wanda\OneDrive – charles wanda\Desktop\Work\Daily Production Report\Daily Production Report

and that containing the master is: C:\Users\ charles.wanda\OneDrive – charles wanda \Desktop\Work\Daily Production Report

Please let me know if you need further clarifications. Thanks a lot for assisting.

 

[mumps]

Will there always be 7 groups with the 3rd, 5th, 6th and 7th group having only one class?

 

[Charles Wanda]

Will there always be 7 groups with the 3rd, 5th, 6th and 7th group having only one class?

There are actually 6 groups. Please see updated file link.

test_updated

Shared with Dropbox

www.dropbox.com

based on the current model of the facility, there would always be 6 groups as seen on the updated files. The number however might change in future if an expansion of the production facility is carried out.
For the existing 6 groups, the 3rd and 6th have only one class as seen on the updated files.
Thank you.

 

[mumps]

Your Master doesn't have a 5B which is included in the Report file and the Master includes a 7A which is not in the Report file. Please clarify.

 

[Charles Wanda]

Your Master doesn't have a 5B which is included in the Report file and the Master includes a 7A which is not in the Report file. Please clarify.

That was typo. Please see update.
Thanks

test_updated

Shared with Dropbox

www.dropbox.com

 

[mumps]

Place this macro in the Master and run it from there:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWB As Workbook, sDate As String, strExtension As String, fnd As Range
    Dim arr As Variant, a As Variant, r As Long, c As Long, i As Long
    Set desWB = ThisWorkbook
    Set desWS = desWB.Sheets("Sheet1")
    Const strPath As String = "C:\Users\charles.wanda\OneDrive – charles wanda\Desktop\Work\Daily Production Report\Daily Production Report\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        sDate = Split(Split(ActiveWorkbook.Name, "_")(1), ".")(0)
        With srcWB
            Set fnd = desWS.Range("B:B").Find(DateValue(sDate), LookIn:=xlFormulas)
            If Not fnd Is Nothing Then
                ReDim a(1 To 30)
                arr = Range("E7:G16").Value
                For r = 1 To UBound(arr)
                    For c = 1 To UBound(arr, 2)
                        i = i + 1
                        a(i) = arr(r, c)
                    Next c
                Next r
            End If
            desWS.Range("C" & fnd.Row).Resize(, 30) = a
            i = 0
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Charles Wanda]

Place this macro in the Master and run it from there:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWB As Workbook, sDate As String, strExtension As String, fnd As Range
    Dim arr As Variant, a As Variant, r As Long, c As Long, i As Long
    Set desWB = ThisWorkbook
    Set desWS = desWB.Sheets("Sheet1")
    Const strPath As String = "C:\Users\charles.wanda\OneDrive – charles wanda\Desktop\Work\Daily Production Report\Daily Production Report\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        sDate = Split(Split(ActiveWorkbook.Name, "_")(1), ".")(0)
        With srcWB
            Set fnd = desWS.Range("B:B").Find(DateValue(sDate), LookIn:=xlFormulas)
            If Not fnd Is Nothing Then
                ReDim a(1 To 30)
                arr = Range("E7:G16").Value
                For r = 1 To UBound(arr)
                    For c = 1 To UBound(arr, 2)
                        i = i + 1
                        a(i) = arr(r, c)
                    Next c
                Next r
            End If
            desWS.Range("C" & fnd.Row).Resize(, 30) = a
            i = 0
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Noted. I would do that and revert.
Thanks a lot.

 

[mumps]

Did the macro work properly for you?