Show result cleaning sheet

[Watersource]

Hello,

I have created a VBA macro that first cleans the content of the sheets and then fills the sheet with new data again. As quite a lot of data is gathered, I have tried to show that cleaning the content has finished (by putting .ScreenUpdating to True and False again) but this does not work yet. No updating takes place during running of this macro, only when it is finished. Could you give me some pointers how to achieve this.

Sub Refresh_Data()

Dim sht As Worksheet
Dim shtActiveSht As Worksheet

Set shtActiveSht = ActiveSheet

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
On Error Resume Next

ClearSheets --sub to clear contents

.ScreenUpdating = True
.ScreenUpdating = False

On Error GoTo 0
.Run ("EG2000.XLA!ThisWorkbook.AddIn_OnRefresh")

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
MsgBox ("The reports have been updated.")
End With

End Sub

Thanks for your help on this.

 

[Yongle]

May not be able to do exatly what you want. But may be able to offer something to give user comfort
Please post macro ClearSheets

 

[Watersource]

May not be able to do exatly what you want. But may be able to offer something to give user comfort
Please post macro ClearSheets

The ClearSheets macro is the following:

Sub ClearSheets()

Dim lastrow As Long
Dim sht As Worksheet
Dim shtActiveSht As Worksheet
Set shtActiveSht = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
    sht.Activate
        Range("A10").Select
        lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Rows("10:" & lastrow).ClearContents
    Next sht
shtActiveSht.Activate

End Sub

Just to be complete, we use Excel 2016.