Code to clear fields in other closed workbooks

03856me

Active Member
Joined
Apr 4, 2008
Messages
278
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
Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
No obvious reason why it's not working, but try this
Code:
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

Active Member
Joined
Apr 4, 2008
Messages
278
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted.
 

Forum statistics

Threads
1,148,290
Messages
5,745,879
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top