Out of my league.... Please help.

[bedsy]

I have been researching this for weeks and just keep going around in circles, can some please help and save my sanity.

Problem: I have 2 workbooks (Daily report) and (wagon Numbers).

What I'm trying to achieve. miserably I might add, is to run VBA code from the wagon numbers sheet to prompt the user to open a folder location and run through each daily report excel sheet in that folder (which are simple named the days dates e.g 10.05.23.xlsx).

for each file opened sumif/Countifs or sumproduct (not sure which one cause I can't get either to work) multiple criteria and input the values in the wagon numbers workbook on the next blank line within a certain range.
The below sumifs work if I am only referencing cell in the same worksheet but will not carry over when I reference another workbook?

sound easy.... Some code I have as per below.

It does automatically open files and run through each file in the folder but the issue is the month (02 February) will be variable depending, well, on the month, not sure how to manage this?.
The sumifs/countifs will not be transferred to the wagon totals workbook and not positive on what code to ensure the values returned will not overwrite the totals from the previous sheet when I can get it to work without specifying each range in the code (e.g below code should place return value in B1,B2,B3 - I dont want to duplicate the code to have it place the return values in C1,C2,C3, D1 D2 D3 ect.

Sub OpenAllWorkbooks()
    Dim MyFiles As String
   Dim dblAnswer As Double
   Dim ws2 As Workbook
    Application.ScreenUpdating = False

    Set ws1 = ActiveSheet - 'workbook that has been opened by user
    Set ws2 = Workbooks("wagon totals.xlsx").Worksheets("Wagon count") - 'Master workbook that the code is run from
   
 
    MyFiles = Dir("G:\testFile\2023\02 February\*.xlsx")
    Do While MyFiles <> ""
        Workbooks.Open "G:\TestFile\2023\02 February" & MyFiles

ws2.Range("B2") = Application.WorksheetFunction.SumIfs(ws1.Range("H4:H26"), ws1.Range("A4:A26"), "Operator", ws1.Range("B4:B26"), "Product1")
ws2.Range("B1") = WorksheetFunction.CountIfs(Range("A4:A26"), "Operator", Range("B4:B26"), "Product1", Range("H4:H26"), ">0")
ws2.Range("B3")) = WorksheetFunction.SumIfs(Range("H4:H26"), Range("A4:A26"), "Operator", Range("B4:B26"), "Product2")

  
        MsgBox ActiveWorkbook.Name
        ActiveWorkbook.Close SaveChanges:=True
        MyFiles = Dir
    Loop
End Sub

Any help would be greatly appreciated.

 

[DanteAmor]

Hi @bedsy . Thanks for posting on MrExcel.

From what I see in your code, you have a sheet called "Wagon count".

Worksheets("Wagon count")

So in that workbook that contains that sheet, put the following macro:

Sub OpenBooks()
  Dim sPath As String, MyFiles As String
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim wb2 As Workbook
  Dim j As Long
  
  Set sh1 = ThisWorkbook.Sheets("Wagon count")

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1) & "\"
  End With
  
  j = 2
  MyFiles = Dir(sPath & "*.xlsx")
  Do While MyFiles <> ""
    Set wb2 = Workbooks.Open(sPath & MyFiles)
    Set sh2 = wb2.Sheets(1)
    sh1.Cells(1, j).Value = WorksheetFunction.CountIfs(sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1", sh2.Range("H4:H26"), ">0")
    sh1.Cells(2, j).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1")
    sh1.Cells(3, j).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product2")
    j = j + 1
    
    MyFiles = Dir()
  Loop
End Sub

It does automatically open files and run through each file in the folder but the issue is the month (02 February) will be variable depending

The macro asks you for the folder from which you want to read the files.
The code puts the results of the first book in column B, the results of the second book in column C, and so on.
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[Killragtshirts]

This may assist. Its not complete code however, this is how I tackle opening multiple excel files and doing things with them.

Sub OpenExcelFiles()

Dim awb As Object, wb As Object
Dim FilePicker As FileDialog
Dim myPath As String, myExtension As String, myFile As String

    ' Create Workbook Object
    Set awb = ActiveWorkbook

    ' Optimize Macro Speed
    TurnOffFunctionality
   
    ' Retrieve Target File Path From User
    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)

    With FilePicker
        .Title = "Please Select Target File/s"
        .AllowMultiSelect = True
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1)
    End With

    Dim iFileImportCount As Integer
    iFileImportCount = FilePicker.SelectedItems.Count

' In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

    Dim vrtSelectedItem As Variant
   
    For Each vrtSelectedItem In FilePicker.SelectedItems
   
        myPath = vrtSelectedItem
        myFile = Dir(myPath)

      Set wb = Workbooks.Open(Filename:=myPath, UpdateLinks:=False)

    ' Ensure Workbook has opened before moving on to next line of code
      DoEvents

    Dim Sheet As Worksheet
    For Each Sheet In wb.Sheets
          
    'DO SOMETHING HERE
          
     Next
          
                  
    ' Close workbook
    wb.Close SaveChanges:=False
       
    ' Ensure Workbook has closed before moving on to next line of code
    DoEvents
Next
  'Loop
   
    ' Reset Macro Optimization Settings
    TurnOnFunctionality

    ' Message Box when tasks are completed
    MsgBox "Data import has completed successfully.", , "Import Successful"

Done: Exit Sub

ResetSettings:
      ' Reset Macro Optimization Settings
        TurnOnFunctionality

End Sub

Public Sub TurnOffFunctionality()
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub

Public Sub TurnOnFunctionality()
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[bedsy]

Hi @bedsy . Thanks for posting on MrExcel.

From what I see in your code, you have a sheet called "Wagon count".


So in that workbook that contains that sheet, put the following macro:

Sub OpenBooks()
  Dim sPath As String, MyFiles As String
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim wb2 As Workbook
  Dim j As Long
 
  Set sh1 = ThisWorkbook.Sheets("Wagon count")

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1) & "\"
  End With
 
  j = 2
  MyFiles = Dir(sPath & "*.xlsx")
  Do While MyFiles <> ""
    Set wb2 = Workbooks.Open(sPath & MyFiles)
    Set sh2 = wb2.Sheets(1)
    sh1.Cells(1, j).Value = WorksheetFunction.CountIfs(sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1", sh2.Range("H4:H26"), ">0")
    sh1.Cells(2, j).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1")
    sh1.Cells(3, j).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product2")
    j = j + 1
   
    MyFiles = Dir()
  Loop
End Sub

The macro asks you for the folder from which you want to read the files.
The code puts the results of the first book in column B, the results of the second book in column C, and so on.
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Dante.......... Thank you!!!

Still require some adjustments but detail did finally make it to the correct sheet.

so..... i should have explained a little better.

when the details are input into the Wagon count sheet i require them to only be in a specific range for example.

sh1.Cells(1, j).Value = WorksheetFunction.CountIfs(sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1", sh2.Range("H4:H26"), ">0") ' entered in into Cell B1
sh1.Cells(2, j).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1") 'entered in Cell B2
sh1.Cells(3, j).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product2") 'entered in Cell B3

as it does. then when it swaps to the following spreadsheet and runs the Countif/Sumifs the input is then placed in the same columns but the next blank line down e.g C1, C2, C3.

In addition, does the

Set sh2 = wb2.Sheets(1)

ensure the opened spreadsheet activates the first sheet in the workbook as it has multiple but the data only needs to come from the first sheet

any thoughts on how I can have the selected files open, run the code and close without saving.

Thanks Legend....
Bedsy

 

[Killragtshirts]

Is this what you are after?

ActiveWorkbook.Close savechanges:=False

So in your case Wb2.Close savechanges:=False

j = j + 1

Wb2.Close savechanges:=False

MyFiles = Dir()

 

[bedsy]

Is this what you are after?

ActiveWorkbook.Close savechanges:=False

So in your case Wb2.Close savechanges:=False

j = j + 1

Wb2.Close savechanges:=False

MyFiles = Dir()

Nailed it Killragtshirts ( haha..love the name).....
I should have thought of that 1.
Any thoughts on the transferring the data to a certain range..... i know the next blank line code but i need that to only applied within a certain range e.g next blank line between A1:A32.

Cheers..
Bedsy

 

[DanteAmor]

Still require some adjustments but detail did finally make it to the correct sheet.

Okay, let's work on it.

I still don't understand what details are missing, but I'll try to fix them.

-------------------------------------------------
1)

when the details are input into the Wagon count sheet i require them to only be in a specific range for example.

What is that specific range?
In your original post you put that you want them in B1, B2 and B3, the next sheet in C1, C2 and C3. And that does the macro.
So where do you want the data?

-------------------------------------------------
2)

In addition, does the
Set sh2 = wb2.Sheets(1)
ensure the opened spreadsheet activates the first sheet in the workbook as it has multiple but the data only needs to come from the first sheet

Set sh2 = wb2.Sheets(1). This line is responsible for taking the data from sheet 1 of the file. By the way, you didn't mention it in your original post, but I have to take the data from some sheet, so I decided to take it from sheet 1, so this detail is already covered.


-------------------------------------------------
3)

any thoughts on how I can have the selected files open, run the code and close without saving.

I missed that part but I already give you the corrected macro.

-------------------------------------------------
4)

when it swaps to the following spreadsheet and runs the Countif/Sumifs the input is then placed in the same columns but the next blank line down e.g C1, C2, C3.

According to that comment, the data goes in the same column.
But to better understand what you want, you should put an image of the expected result, since your explanation is very confusing for me.
For example:

-------------------------------------------------
Try the following macro to see if it has what it takes:

Sub OpenBooks()
  Dim sPath As String, MyFiles As String
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim wb2 As Workbook
  Dim i As Long
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh1 = ThisWorkbook.Sheets("Wagon count")

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1) & "\"
  End With
 
  i = 1   'initial row to put the data
 
  MyFiles = Dir(sPath & "*.xlsx")
  Do While MyFiles <> ""
    Set wb2 = Workbooks.Open(sPath & MyFiles)
    Set sh2 = wb2.Sheets(1)
    sh1.Range("C" & i).Value = WorksheetFunction.CountIfs(sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1", sh2.Range("H4:H26"), ">0")
    sh1.Range("C" & i + 1).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1")
    sh1.Range("C" & i + 2).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product2")
    i = i + 3
    wb2.Close False

    MyFiles = Dir()
  Loop
 
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[bedsy]

Okay, let's work on it.

I still don't understand what details are missing, but I'll try to fix them.

-------------------------------------------------
1)

What is that specific range?
In your original post you put that you want them in B1, B2 and B3, the next sheet in C1, C2 and C3. And that does the macro.
So where do you want the data?

-------------------------------------------------
2)


Set sh2 = wb2.Sheets(1). This line is responsible for taking the data from sheet 1 of the file. By the way, you didn't mention it in your original post, but I have to take the data from some sheet, so I decided to take it from sheet 1, so this detail is already covered.


-------------------------------------------------
3)

I missed that part but I already give you the corrected macro.

-------------------------------------------------
4)

According to that comment, the data goes in the same column.
But to better understand what you want, you should put an image of the expected result, since your explanation is very confusing for me.
For example:
View attachment 91935

-------------------------------------------------
Try the following macro to see if it has what it takes:

Sub OpenBooks()
  Dim sPath As String, MyFiles As String
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim wb2 As Workbook
  Dim i As Long
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh1 = ThisWorkbook.Sheets("Wagon count")

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1) & "\"
  End With
 
  i = 1   'initial row to put the data
 
  MyFiles = Dir(sPath & "*.xlsx")
  Do While MyFiles <> ""
    Set wb2 = Workbooks.Open(sPath & MyFiles)
    Set sh2 = wb2.Sheets(1)
    sh1.Range("C" & i).Value = WorksheetFunction.CountIfs(sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1", sh2.Range("H4:H26"), ">0")
    sh1.Range("C" & i + 1).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product1")
    sh1.Range("C" & i + 2).Value = WorksheetFunction.SumIfs(sh2.Range("H4:H26"), sh2.Range("A4:A26"), "Operator", sh2.Range("B4:B26"), "Product2")
    i = i + 3
    wb2.Close False

    MyFiles = Dir()
  Loop
 
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Hi Dante,

My dearest apologies....... after re-reading my request you have given me exactly what I have asked for. I have asked for the wrong thing.

I'm actually after the data to be input into B2,C2,D2 and then the next file to be put into B3,C3,D3 (the next blank line below the previous data).

Thanks Bedsy...

 

[bedsy]

IT WORKS, IT WORKS, IT WORKS.........

Was able to manipulate the code enough to get to what i needed.

My sanity has been restored...

Thank you Dante, I would never have completed without your work........ so very much appreciated

Bedsy.