VBA Coding Problem Excel in 2016

[Tmini]

Hi
I posted a question the other day without any luck in getting a response. I know it would have to do with how it was asked. In the meantime I attempted to fix my code and still haven't had any luck. The issue is my code works fine in excel 2010 and excel 2013 but not 2016. My problem is here at work I won't be using 2010 much longer and will be shifting to 2016 soon. I have 2016 at home and that is how I discovered there was an issue.
The problem is it isn't throwing any errors at me so I don't know where I have made the mistake.
What my workbook does is when you click the button in it it opens up a dialogue box to select a folder that contains a heap of excel files that I have already populated with data. It then goes through and opens each excel file copies the data and pastes it into the workbook then closes each file. My workbook has a formula in one of the worksheets that is also automatically copied down each line for each excel file that it opens. Once it has finished going through all of the excel files in the folder my workbook is finished with a heap of figures all summarised and added up from the multitude of files it has opened and copied, in the one workbook. This has always worked great in excel 2010 and 2013 but when it came to trying to use it in excel 2016 it just suddenly stopped working correctly. When I first tried it it looked as if it was copying the formula in the workbook the wrong way, trying to fill the formula up rather than down but after slightly modifying the code it still starts with folder dialogue box as intended but after that it is like it does nothing. There is no error that pops up and no indication on whether it has done what it was supposed to have done.
My code is below can anyone please help me and let me know where I have stuffed up. If you need a copy of my workbooks let me know and I can send them through or link to where they can be downloaded from. if whatever I have explained is unclear please advise and I will try to explain it better. I really need help to get this working as it saves me hours and hours on heaps of work when I have it working properly
Thanks in advance for any help as it is greatly appreciated

Option ExplicitSub RunAllMacros()
CommandButton1_Click
test
End Sub
Sub CommandButton1_Click()
    Dim x, fldr As FileDialog, SelFold As String, i As Long
    Dim ws As Worksheet, ws1, ws2 As Worksheet
    Dim Wb As Workbook, Filename As String
    Dim screenUpdateState As String
    Dim statusBarState As String
    Dim eventsState As String
    Dim lngrow As Integer
    
    
    
    
        
    
    screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
eventsState = Application.EnableEvents


'turn off some Excel functionality for faster performance
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False




    'User Selects desired Folder
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        If .Show <> -1 Then GoTo Cleanup
        SelFold = .SelectedItems(1)
    End With


    'All .xls* files in Selected FolderPath including Sub folders are put into an array
    x = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & SelFold & "\*.xls"" /s/b").stdout.readall, vbCrLf)
     
     Set ws1 = ThisWorkbook.Sheets("Labour & Material")
     Set ws2 = ThisWorkbook.Sheets("Total Hours For All Units")
     
    'Loop through that array
    For i = LBound(x) To UBound(x) - 1


    'Open (in background) the Workbook
        With GetObject(x(i))
           
            ThisWorkbook.Sheets(1).UsedRange
            Filename = Split(x(i), "\")(UBound(Split(x(i), "\")))
       Set Wb = Workbooks(Filename)
        Set ws = Nothing
        On Error Resume Next
        'change sheet name here
        Set ws = Wb.Sheets("Total Quantities")
        On Error GoTo 0
        If Not ws Is Nothing Then
        If lngrow = 0 Then
        lngrow = 5
    Else
        lngrow = lngrow + 1
    End If
        ws1.Cells(lngrow, "A").Value = ws.Range("A1").Value
        ws1.Cells(lngrow, "B").Value = ws.Range("I2").Value
        ws1.Cells(lngrow, "C").Value = ws.Range("C2").Value
        ws1.Cells(lngrow, "E").Value = ws.Range("C3").Value
        ws1.Cells(lngrow, "G").Value = ws.Range("C4").Value
        ws2.Cells(lngrow, "B").Value = ws.Range("B8").Value
        ws2.Cells(lngrow, "C").Value = ws.Range("B9").Value
        ws2.Cells(lngrow, "D").Value = ws.Range("B10").Value
        ws2.Cells(lngrow, "E").Value = ws.Range("B11").Value
        ws2.Cells(lngrow, "F").Value = ws.Range("B12").Value
        ws2.Cells(lngrow, "G").Value = ws.Range("B13").Value
        End If
            .Close
        End With
    Next i


Cleanup:
    Set fldr = Nothing
End Sub
Sub test()
Dim SheetNum As Variant
Dim Sh As Variant
Dim SoRng As Variant
Dim ColNo As Variant
Dim Col As Variant






SheetNum = Array(1, 2, 5, 6)
For Each Sh In Sheets(SheetNum)
    Sh.Select
    Set SoRng = Sh.Range("A5", Sh.Range("A5").End(xlToRight).Address)
    AdvFil SoRng
Next


Sheets(4).Select
Set SoRng = Sheets(4).Range("A5:A5")
AdvFil SoRng


Sheets(3).Select
ColNo = Array("D", "F", "H")
    For Each Col In ColNo
    Set SoRng = Sheets(3).Range(Col & "5:" & Col & "5")
    AdvFil SoRng
Next


End Sub
Sub AdvFil(ByVal x As Range)
Dim LrNum As String
Dim DesRng As Variant


LrNum = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row
If InStr(1, x.Address, ":") > 0 Then
    DesRng = Left(x.Address, Len(x.Address) - 1) & LrNum
Else
    DesRng = x.Address & ":" & Left(x.Address, Len(x.Address) - 1) & LrNum
End If
x.AutoFill Destination:=Range(DesRng)
End Sub

 

[pvr928]

Hi Tmini

There is no functional difference from a VBA perspective between 2010, 2013 and 2016.

Comment out the

On Error Resume Next

row (this will effectively be 'hiding'/ignoring any issues/errors), and then run your code.

If it does error, let us know showing what line it errors on and the error description.

Cheers

pvr928

 

[Tmini]

Hi Tmini

There is no functional difference from a VBA perspective between 2010, 2013 and 2016.

Comment out the

On Error Resume Next

row (this will effectively be 'hiding'/ignoring any issues/errors), and then run your code.

If it does error, let us know showing what line it errors on and the error description.

Cheers

pvr928

Hi PVR
Thanks so much for that. That worked and now it is working fine. I don't know how after all these years it has worked fine then all of a sudden that seems to have caused an error. Cheers for your help

 

[pvr928]

Hi Tmini

In your original post, you said, inter alia:

The problem is it isn't throwing any errors at me

There is no error that pops up

{but} it just suddenly stopped working correctly

Your original code contains the line:

On Error Resume Next

From Microsoft, this line of code has the following effect:

[FONT=segoe-ui_normal]Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred, and execution continues from that point[/FONT]

In other words, it ignores the error and allows the code to continue running.

Consequently, if the reason for original code not running as intended was because of an error occurring, the line would have prevented that error from breaking the code, ie the code would have continued running.

If you have commented out that line but the code now runs as intended, the code not running as intended originally was not because an error was preventing it from running - because that error, if it existed, would now materialise because the

On Error Resume Next

line was not operating.

Consequently, your code now running as intended is because of a reason other than having commented out/deleted the

On Error Resume Next

line.

Cheers

pvr928

 

[Tmini]

Thank you pvr. I did suspect this. I will continue looking into it and see where I may have buggered up but as it is working now it makes it difficult to figure out. I will experiment and see what my outcome is.