Code to clear fields in other closed workbooks

[03856me]

I have pieced together the following code which fires without error, opening files and closing them, BUT it does not clear the range of cells M12:AB25 on each worksheet. Can someone help with what I am doing wrong

Sub Make_New_Timesheets()
    Dim MyFolder As String, MyFile As String

    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       MyFolder = .SelectedItems(1)
       Err.Clear
    End With

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    MyFile = Dir(MyFolder & "\", vbReadOnly)
    Do While MyFile <> ""
        DoEvents
        On Error GoTo 0

        Workbooks.Open FileName:=MyFolder & "\" & MyFile, UpdateLinks:=False
        
'====== clear each worksheet in external workbook
    Dim wsClear As Worksheet

    For Each wsClear In Worksheets
        If wsClear.Name <> "Summary" _
            And wsClear.Name <> "Recap" _
            And wsClear.Name <> "TEXT" Then
                
            wsClear.Range("M12:AB25").ClearContents
        End If
    Next wsClear
                
0
        Workbooks(MyFile).Close SaveChanges:=False
        MyFile = Dir

    Loop

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationManual
End Sub

 

[Fluff]

No obvious reason why it's not working, but try this

Sub Make_New_Timesheets()
    Dim MyFolder As String, MyFile As String
    Dim Wbk As Workbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       MyFolder = .SelectedItems(1)
       Err.Clear
    End With

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    MyFile = Dir(MyFolder & "\", vbReadOnly)
    Do While MyFile <> ""
        DoEvents

        Set Wbk = Workbooks.Open(Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False)
        
'====== clear each worksheet in external workbook
    Dim wsClear As Worksheet

    For Each wsClear In Wbk.Worksheets
        If wsClear.Name <> "Summary" _
            And wsClear.Name <> "Recap" _
            And wsClear.Name <> "TEXT" Then
                
            wsClear.Range("M12:AB25").ClearContents
        End If
    Next wsClear
                
        Wbk.Close SaveChanges:=False
        MyFile = Dir

    Loop

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationManual
End Sub

 

[03856me]

Fluff - your changes worked the same way, except I noticed the code Wbk.Close SaveChanges:=False is wrong (my code was wrong too)

I made the changes you suggested plus saveChanges to true and problem solved.

thank you so much.

 

[Fluff]

Glad you got it sorted.

 

[03856me]

just a note - your code runs faster - thanks for that tip