VBA FOR MANY WORKBOOKS

[Anthony Norton]

Hi Guys,

Appreciate your help with this problem by writing a VBA Code. I have uploaded some Excel files for you reference.

1) VBA Code is to be written in the file "Recon".

1) Each period I get a data file titled "Master File". In that file there are 3 groups of data, namely Production, Waste and Sold. All items under Production has Numbers ending with ".8", Waste with ".2" and Sold with ".5". I'm only concerned with Production and Sold. In each group, I am only concerned with the category Export, so the VBA code should filter out any data categorized as Domestic and exclude the group called Waste. Any difference computed between Actual Value and Correct Value are highlighted in the file that I get, but I would like VBA to exclude differences less than 10 and more than -10 i.e. -10<Difference<10 should be rejected. For the rest of the Difference, the corresponding Number is to be retrieved by VBA.

2) Each Number retrieved from the Master File has its own Excel file identically named. In this case I have uploaded 2 files called 100.8.xlsx and 111.5.xlsx. I would like VBA to open each of these files and identify which line items do not belong within the spreadsheet. For eg, in 100.8, "Master File" Description indicates "Wood". In the file 100.8.xlsx, there is a list of different types of Wood. The items that are not part of the Wood category are Crystal Glass and Crystal Pure. The easiest way to filter these out is by using the Address column. You will notice that Address is stated as 342 for Wood and 189 for Glass regardless of the type of variant for each material.

3) Once the line items are identified, VBA should fill the "Recon" file. Under the column called Number, the corresponding Number from the Master File indicated there. If there are 2 line items in 100.8.xlsx that do not belong (in this case 2 Crystal), then there should be 2 rows in Recon. The Description in Recon should then state "RECON FOR CRYSTAL" while the Value column should always be a negative of the Value in the Number file (which is 100.8.xlsx in this case). The same example is repeated for 111.5.

Hope this is clear to you. Let me know if you still do not understand.

 

[PCosta87]

Hi Guys,

Appreciate your help with this problem by writing a VBA Code. I have uploaded some Excel files for you reference.

1) VBA Code is to be written in the file "Recon".

1) Each period I get a data file titled "Master File". In that file there are 3 groups of data, namely Production, Waste and Sold. All items under Production has Numbers ending with ".8", Waste with ".2" and Sold with ".5". I'm only concerned with Production and Sold. In each group, I am only concerned with the category Export, so the VBA code should filter out any data categorized as Domestic and exclude the group called Waste. Any difference computed between Actual Value and Correct Value are highlighted in the file that I get, but I would like VBA to exclude differences less than 10 and more than -10 i.e. -10<Difference<10 should be rejected. For the rest of the Difference, the corresponding Number is to be retrieved by VBA.

2) Each Number retrieved from the Master File has its own Excel file identically named. In this case I have uploaded 2 files called 100.8.xlsx and 111.5.xlsx. I would like VBA to open each of these files and identify which line items do not belong within the spreadsheet. For eg, in 100.8, "Master File" Description indicates "Wood". In the file 100.8.xlsx, there is a list of different types of Wood. The items that are not part of the Wood category are Crystal Glass and Crystal Pure. The easiest way to filter these out is by using the Address column. You will notice that Address is stated as 342 for Wood and 189 for Glass regardless of the type of variant for each material.

3) Once the line items are identified, VBA should fill the "Recon" file. Under the column called Number, the corresponding Number from the Master File indicated there. If there are 2 line items in 100.8.xlsx that do not belong (in this case 2 Crystal), then there should be 2 rows in Recon. The Description in Recon should then state "RECON FOR CRYSTAL" while the Value column should always be a negative of the Value in the Number file (which is 100.8.xlsx in this case). The same example is repeated for 111.5.

Hope this is clear to you. Let me know if you still do not understand.

Hi @Anthony Norton,

Please import the following code to your "Recon" workbook.

This is how it works:
1) Files "100.8" and "111.5" must be in the same folder (you can have other files there, it doesn't matter);
2) Open both "Recon" and "Master File" (they must be open for the code to work properly);
3) You need to make sure the numbers in column A of "Master File" correspond exactly to the file name... in your example it is necessary to replace commas with ".";
4) "Recon" first sheet should be completely empty;
5) Run the code;
6) When prompted, select folder where "100.8" and "111.5" are stored

This code should work fine for this example but for the real situation you may need to do some changes in order for it to work as intended.

Sub test()

    Application.ScreenUpdating = False

    On Error Resume Next

    Dim waste, sold, lrow As Integer
    Dim c As Range
    Dim MyFolder As String
    Dim MyFile As String
      
    MyFolder = GetFolder("")
    MyFile = Dir(MyFolder & "\*.xlsx")
    waste = Workbooks("Master File").Sheets(1).Columns("A:F").Find(What:="waste", LookIn:=xlValues, SearchOrder:=xlByRows).Row
    sold = Workbooks("Master File").Sheets(1).Columns("A:F").Find(What:="sold", LookIn:=xlValues, SearchOrder:=xlByRows).Row - 1
    lrow = Workbooks("Master File").Sheets(1).Columns("A:F").Cells(Rows.Count, 1).End(xlUp).Row
    
    Workbooks("Master File").Sheets(1).Rows(waste & ":" & sold).Hidden = True

    For Each c In Workbooks("Master File").Sheets(1).Range("F3:F" & lrow).Cells
        If c.Value < 10 And c.Value > -10 Then
            c.EntireRow.Hidden = True
        End If
    Next c

    For Each c In Workbooks("Master File").Sheets(1).Range("C3:C" & lrow).Cells
        If c.Value = "Domestic" Then
            c.EntireRow.Hidden = True
        End If
    Next c
        
    For Each c In Workbooks("Master File").Sheets(1).Range("A3:A" & lrow).Cells
        If c.EntireRow.Hidden = False Then
            Workbooks.Open Filename:=MyFolder & "\" & c.Value & ".xlsx"
        
            Dim lrow2 As Integer
            Dim lrow3 As Integer
            lrow2 = Workbooks("Recon").Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row + 1
            lrow3 = ActiveWorkbook.Sheets(1).Columns("D").Cells(Rows.Count).End(xlUp).Row
        
            With ActiveWorkbook.Sheets(1)
                .Columns("A:D").AutoFilter Field:=4, Criteria1:="=189", _
                Operator:=xlOr, Criteria2:="=292"
                .Range("A1:D" & lrow3).Copy Workbooks("Recon").Sheets(1).Cells(lrow2, 1)
                Workbooks("Recon").Sheets(1).Cells(lrow2 + 1, 1).Value = ActiveWorkbook.Name
                Workbooks("Recon").Sheets(1).Cells(lrow2 + 2, 1).Value = ActiveWorkbook.Name
            End With
        End If
    Next c

    Workbooks("Recon").Sheets(1).Columns("A:E").RemoveDuplicates Columns:=2, Header:=xlNo

    lrow = Workbooks("Recon").Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row

    For Each c In Workbooks("Recon").Sheets(1).Range("A2:C" & lrow)
        If InStr(c.Value, "Crystal") > 0 Then
            c.Value = "Recon for Crystal"
        ElseIf InStr(c.Value, "Sand") > 0 Then
                c.Value = "Recon for Sand"
        ElseIf InStr(c.Value, "Index") > 0 Then
                c.Value = "Number"
        End If
    Next c

    For Each c In Workbooks("Recon").Sheets(1).Range("C3:C" & lrow)
        c.Value = -c.Value
    Next c

    Workbooks("Recon").Sheets(1).Range("A3:A" & Columns("A").Cells(Rows.Count).End(xlUp).Row).Replace What:=".xlsx", Replacement:=""

    Workbooks("Recon").Sheets(1).Columns("D").Delete
    Workbooks("Recon").Sheets(1).Columns("C:C").Cut
    Workbooks("Recon").Sheets(1).Columns("B:B").Insert shift:=xlToRight

    Workbooks("Recon").Sheets(1).Rows("1").Delete shift:=xlUp

Application.ScreenUpdating = True

End Sub

Function GetFolder(strPath As String) As String

    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With

NextCode:
    GetFolder = sItem
    Set fldr = Nothing

End Function

I hope this helps

Regards