troubleshooting Question

Looping Excel Formats through entire file

Avatar of uceskr
uceskr asked on
Microsoft Excel
5 Comments1 Solution199 ViewsLast Modified:
I am trying to loop an excel format macro through a directory that contains 90 excel files.  Each file has a workbook "CurrentJE" that needs to be formatted.  The Current JE workbook is exactly the same in all 90 excel files.

I have created a VBA program to do this loop but each time I run the program, it only works in the excel file where I set it up.  

How do I make the loop program work for the entire directory (all 90 files)?

Here is the program:
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            'Change path to suit
            .LookIn = "C:\Documents and Settings\eskridgc\My Documents\Chuck2013\SendDepts"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            .Filename = "CurrentJ*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook "CurrentJE" and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.CurrentJ * (lCount), UpdateLinks:=0)
    'For Formatting Workbook "CurrentJE" in each File
    Selection.ColumnWidth = 16
    Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
        .PatternTintAndShade = 0
    End With
    ActiveWorkbook.Worksheets("CurrentJE").Sort.SortFields.Add Key:=Range( _
        "B2:B53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    With ActiveWorkbook.Worksheets("CurrentJE").Sort
        .SetRange Range("A1:N53")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    ActiveWindow.SmallScroll Down:=0
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Columns("B:B").ColumnWidth = 6.43

                        wbResults.Close SaveChanges:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Ken Butters

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros