run time error 13'

[bedsy]

Calling all excel masters,

HI all - i am a new vba code enthusiast that needs some help.

i have the old runtime error with my code.

I'm trying to open a group of files (MyFiles) automatically, sumifs some ranges and then input the totals into a second workbook. the code will be run from (input.xls)

the code will cycle through each spreadsheet in the target folder transferring the sumifs to the input files next blank line.

any assistance would be greatly appreciated.

thanks you all in advance.

Sub OpenAllWorkbooks()
    'Step 1:Declare your variables
    Dim MyFiles As String
   Dim dblAnswer As Double
    Application.ScreenUpdating = False
    Set ws1 = ActiveSheet
    Set wb2 = Workbooks(MyFiles)
    Set ws2 = wb.Sheets("Arrivals")
    
    
    'Step 2: Specify a target folder/directory, you may change it.
    MyFiles = Dir("G:testfile\02 February\*.xlsx")
    Do While MyFiles <> ""
        'Step 3: Open Workbooks one by one
        Workbooks.Open "G:\testfile\02 February\" & MyFiles

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

 
      
        MsgBox ActiveWorkbook.Name
        ActiveWorkbook.Close SaveChanges:=True

        'Step 4: Next File in the folder/Directory
        MyFiles = Dir
    Loop
End Sub

 

[NdNoviceHlp]

Set wb2 = Workbooks(MyFiles)

"MyFiles" doesn't exist when this line of code is executed (by extension neither does ws2). HTH. Dave

 

[bedsy]

Thanks Dave..........
So i'm a little confused to say the least but I can not figure out how to reference the workbook that will open via the Dir. Note: Ws2 is supposed to reference at target worksheet on the opened file as this may not necessarily be the active sheet when opened.

 

[NdNoviceHlp]

This part is wrong. I also thought that the ws2 sheet was from wb2 which on re-read is not what U have(?). Anyways, it seems to me that this is what you want...

Do While MyFiles <> ""
'Step 3: Open Workbooks one by one
Set wb2 = Workbooks.Open("G:\testfile\02 February\" & MyFiles)
Set ws2 = wb2.Sheets("Arrivals")
'etc.

HTH. Dave