Macro stopped working- No error messages

[jrg24]

I have a Macro that pulls a particular sheet from all workbooks in a directory and copies it into one workbook (the code is actually in the workbook, it is not in my personal workbook). It then renames these sheets based upon data from the sheet copied. I have been using this macro for several months, and it has worked fine until today. I cannot get the macro to run. I hit the run button, and nothing happens. No error messages, no nothing. I have attempted rerunning it in an existing workbook, copying an existing workbook, and copying the code and pasting into a mew module in a new workbook. All have the same result. I used this marco just yesterday with no issues. The source data location has not changed, nor has the location of the sheet containing the Macro. Any Ideas? Any help would be appreciated.

Option Explicit

Sub PullSheets()
   Dim sFolder As String
   Dim sFile As String
   Dim wbSource As Workbook
   Dim wbMaster As Workbook
   Dim ws As Worksheet
Dim strShName As String


   '====================================================
   'EDIT THIS
   sFolder = "C:\Users\Username\Documents\Annual Budget\ASPAC Submissions\"   'remember trailing backslash"
   '====================================================
   
   'set up the master workbook
   Set wbMaster = ThisWorkbook
  
   
   On Error GoTo errHandler   'reset application setting on error
   Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
   'loop through all excel files in folder
   sFile = Dir(sFolder & "*.xls*")
   Do Until sFile = ""
   
      'open the source workbook
      If sFile <> wbMaster.Name Then   'don't process the master workbook
         Set wbSource = Workbooks.Open(sFolder & sFile)
         
         'copy the first worksheet EDIT IF NECESSARY
         
         wbSource.Worksheets(9).Copy After:=wbMaster.Sheets(wbMaster.Sheets.Count)
         strShName = Mid(Range("C6").Formula, InStr(Range("C6").Formula, "[") + 1)


    strShName = Split(strShName, "-")(0) & "-" & Split(strShName, "-")(1)
    ActiveSheet.Name = strShName
         
         wbSource.Close SaveChanges:=False
         Application.CutCopyMode = False
      End If
      
      'get the next file
      sFile = Dir()
   Loop
 
    


For Each ws In Worksheets


Next
   'tidy up
   Set wbSource = Nothing
   Set wbMaster = Nothing
   
errHandler:
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   End Sub

 

[Fluff]

Remove this line & see what happens

   On Error GoTo errHandler   'reset application setting on error

 

[jrg24]

With that code removed, I still have the same issue. It appears nothing has changed.

 

[jrg24]

I sent the macro code to someone else and had them run it. It worked for the other person. I am really stumped by this.

 

[Fluff]

In that case try stepping through the code line by line (using F8) to see if you can figure out where it's going wrong.
Would also be worth rebooting your machine.

 

[jrg24]

I figured out the issue. I pointed the Macro to other folders and everything worked. So, going back to my source folder, I figured out that some fo the files submitted to the folder were not in the proper file format. When I removed these files, the Macro ran as expected. I did not see it at first because the file was saved as XLSM from an XLSX file. Once I figured this out, I was able to identify that this was the cause.

 

[Fluff]

Glad you sorted it & thanks for the feedback