File Names with an "&" sign is causing an error

[Terry Echols]

I have some VBA code to run through my excel files in a specific folder that pulls information to a master sheet. The issue I'm having is that some of my file names, which is the companies name and I can't change them due to other macros that use these same files, have an "&" in their name like M & M Construction. My code is below. When the macro gets to a file name with "&" (without the quote marks, i.e. A & C DRY CLEANING) it throws a runtime error.

Sub DataExtract()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim i As Long
Dim j As Long
Dim k As Long
Dim o As Long
Dim s As Long
Dim objFSO As Object
Dim objFolder As Object
Dim objFile  As Object
Dim rng As Variant
Dim Rrng As Variant
Dim InvFound As Range
Dim wb As Workbook

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\Statements\")

    Set wb = ActiveWorkbook
    i = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row + 1
    j = wb.Sheets("Statement").Cells(Rows.Count, "C").End(xlUp).Row
    With wb.Sheets("Statement").Range("B13:B" & j)
    Set InvFound = .Columns(1).Find(What:="PUR", LookIn:=xlValues)
    End With
    If Not InvFound Is Nothing Then
    wb.Sheets("Statement").Range("B12:I12").AutoFilter Field:=1, Criteria1:="PUR"
    wb.Sheets("Statement").Range("A13:E" & j).SpecialCells(xlCellTypeVisible).Copy
    Sheet1.Range("B" & i).PasteSpecial
    k = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
    wb.Sheets("Statement").Range("F6").Copy
    Sheet1.Range("A" & i & ":A" & k).PasteSpecial
    Application.CutCopyMode = False
    wb.Sheets("Statement").ShowAllData
    o = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    rng = Sheet1.Range("B2:B" & o).Value
    Set Rrng = Sheet1.Range("G2:G" & o)
        For s = 1 To UBound(rng)
            Rrng(s, 1) = Month(rng(s, 1)) + 0
        Next
End If
wb.Close
Next
SplitSheets
Application.DisplayAlerts = False
Application.ScreenUpdating = True

End Sub

Sub SplitSheets()
Dim l As Long
Dim m As Long
Dim p As Long
Dim q As Long

l = Sheet14.Cells(Rows.Count, "A").End(xlUp).Row

For m = 2 To l

    Sheet1.Range("A1:G1").AutoFilter Field:=7, Criteria1:=Sheet14.Cells(m, 1).Value
    p = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    Sheet1.Range("A2:F" & p).SpecialCells(xlCellTypeVisible).Copy
        
            If Sheet14.Range("A" & m).Value = 1 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet2.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 2 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet3.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 3 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet4.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 4 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet5.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet5.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 5 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet6.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet6.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 6 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet7.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet7.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 7 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet8.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet8.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 8 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet9.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet9.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 9 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet10.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet10.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 10 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet11.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet11.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 11 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet12.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet12.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
            ElseIf Sheet14.Range("A" & m).Value = 12 And Sheet1.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) <> vbNullString Then
                q = Sheet13.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheet13.Range("A" & q).PasteSpecial
                Application.CutCopyMode = False
    End If
    Sheet1.ShowAllData
Next
    MsgBox "Finished generating report."
End Sub

What do I need to do to this code to not err on file with "&" in their name?

Thanks,
Terry Echols

 

[mole999]

may work, use &&