Hi!
I was reviewing some of my macros today, and for some reason, this one isn't working as expected. Hoping to get some help here.
The line "SheetName = ws.Range("B11").Value is being skipped for some reason, and each ws comes in with their original name, with a (1), (2), etc appended to it
Any ideas why this step seems to be skipped?? I tried to make this part of the code stand out below
I was reviewing some of my macros today, and for some reason, this one isn't working as expected. Hoping to get some help here.
The line "SheetName = ws.Range("B11").Value is being skipped for some reason, and each ws comes in with their original name, with a (1), (2), etc appended to it
Any ideas why this step seems to be skipped?? I tried to make this part of the code stand out below

Code:
Sub aGatherSummaries()
Dim sWb As Workbook
Dim ws As Worksheet
Dim FolderName As String, SheetName As String
Dim FFolder As Object, FFile As Object
With CreateObject("Scripting.FileSystemObject")
FolderName = Trim(ActiveSheet.Range("B3").Value)
'Test folder validity
If Not .FolderExists(FolderName) Then
MsgBox "The folder path in Cell B3 is invalid." & vbCr & vbCr & "'" & FolderName & "'", vbOKOnly Or vbExclamation, "Folder Path Error"
Exit Sub
End If
Set FFolder = .GetFolder(FolderName)
'Process excel files in that folder
Application.ScreenUpdating = False
For Each FFile In FFolder.Files
'declare the file type of the worksheets to gather
If InStr(1, FFile.Name, ".xls", vbTextCompare) > 0 Then
If ThisWorkbook.Name <> FFile.Name Then
On Error Resume Next
Set sWb = Nothing
Set sWb = Workbooks.Open(FFile.Path)
On Error GoTo 0
If Not sWb Is Nothing Then
For Each ws In sWb.Worksheets
'declare sheet name
If ws.Name = "Summary" Or ws.Name = "Annual Reconciliation Summary" Then
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
''''''''
''''''''
''''''''
''''''''
''''''''
''''''''
'Imported sheet name defined by cell B11
SheetName = ws.Range("B11").Value
''''''''
''''''''
''''''''
''''''''
''''''''
''''''''
''''''''
'Any sheets with the same name are overwritten
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(SheetName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = SheetName
End If
Next ws
sWb.Close False
End If
End If
End If
Next FFile
End With
End Sub