Control flow from main to sub function is not happening

[sunilbsrv2k]

Hi,

I have written a macro to perform few tasks which involves calling other functions.

For sometime it was working fine. But now the Function seems to be not executing. Please help.

Here is my code:

Sub LoopAllExcelFilesInFolder()

Application.DisplayAlerts = False

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim i As Integer

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

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

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)
  i = 0
  'ThisWorkbook.Worksheets(2).Range("A:C").Clear
'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
    Application.ScreenUpdating = False
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      On Error Resume Next
        ccName = wb.Worksheets(1).Range("D1")
        wb.Worksheets(1).Range("G8").Copy
    'Ensure Workbook has opened before moving on to next line of code
      i = i + 1 ' Count number of files
      
 '*************************************** Block to check Password Protection ***********************************
    With ActiveSheet
    
        If .ProtectContents Or .ProtectDrawingObjects Or .ProtectScenarios Then
            PwdPrt = "Protected"
        Else
            PwdPrt = "unProtected"
        End If
End With

'*************************************** End of Block to check Password Protection ***********************************

    ThisWorkbook.Worksheets(2).Cells(i, 1).PasteSpecial xlPasteValues
    ThisWorkbook.Worksheets(2).Cells(i, 2) = ccName
    ThisWorkbook.Worksheets(2).Cells(i, 3) = wb.Name
    ThisWorkbook.Worksheets(2).Cells(i, 4) = PwdPrt
    
     Call Check_Dups(wb, ThisWorkbook)
    'Ensure Workbook has closed before moving on to next line of code
      'DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete! No. of files found : " & i & vbNewLine & "Remaining:" & 62 - i

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

My Sub function is:

Sub Check_Dups(WB As Workbook, wb1 As Workbook)

'Declaring variables
Dim Cell As Variant
Dim Source As Range
'Dim WB, wb1 As Workbook
Dim coun, lastRow As Long
'Initializing source range


Set Source = WB.worksheets(1).Range("B8:B123")


'Removing any previous formatting from the source
'Source.Interior.Color = RGB(221, 235, 247)
coun = 0
'Looping through each cell in the source range
For Each Cell In Source
    
    'Checking whether value in cell already exist in the source range
    If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
        Cell.Copy
        'Set wb1 = Application.Workbooks.Open("D:\Sankalp\VBA - Macros\Payroll Validation project\Find duplicates.xlsx")
        
        wb1.Activate
            lastRow = Cells(Rows.count, 1).End(xlUp).Row
            wb1.Worksheets("Duplicates").Cells(lastRow + 1, 1) = Cell.Value
        
        coun = coun + 1
    End If
Next

End Sub

 

[Yongle]

The INCONSISTENT behaviour could be explained by INCONSISTENT sheet being active at time your code executes
- avoid this risk by ensuring that the code ALWAYS refers to the correct workbook and sheet instead of trusting to luck

Activating different workbooks and worksheets is rarely required in VBA.
Amending code later is made more tricky if it not obvious what is active at each stage in the code.
Most importantly, code is more robust if EVERY range is referenced back to the worksheet and workbook (either directly or indirectly via variables).

For example wb1 (in Check_Dups) is ThisWorkbook and (looking at the whole of the code) ...
... the ActiveSheet (in that workbook) is whichever sheet happens to be active when LoopAllExcelFilesInFolder is run

There is nothing this line in your code to tell VBA that lastRow should be calculated based on values in sheet Duplicates in wb1

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

This line (in LoopAllExcelFilesInFolder) confirms that ThisWorkbook contains more than one sheet

ThisWorkbook.Worksheets(2).Cells(i, 1).PasteSpecial xlPasteValues