Macro to tell user which sheet is empty

[Bimmy]

Hello,

I have a source workbook and master workbook. Both workbooks are stored in different folders.

Currently master workbook has 50 worksheets and will keep increasing. Sheets are named as 1,2,3 and so on. Data are updated in ranges D2:H120 in these sheets.

In source workbook I have listed all the above worksheets one below the other in sheet1
from cell F2 onwards.

Every time the source workbook is opened, macro will update cells G2 onwards with a
YES if the corresponding worksheet from the master workbook contains data in ranges
D2:H120. If there are no data macro will leave the cell blank. Master workbook will remain closed.

Path of master workbook is - D:\Master.xlsm
Option should be provided to change the path

Have attached sample sheet with explanation

 

[Marc L]

Hi !

Try this demonstration :​

Sub Demo()
    Application.ScreenUpdating = False
    Sheet1.[F2].CurrentRegion.ClearContents
For R& = 1 To Worksheets.Count
    With Worksheets(R)
        Sheet1.Cells(1 + R, 6).Value = .Name
        If .Evaluate("COUNTA(D2:H120)") Then Sheet1.Cells(1 + R, 7).Value = "Yes"
    End With
Next
    Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Bimmy]

Thanks for responding so FAST.

I tested your code. What the code does is, it is looking for data in source workbook.

What I'm looking for is, macro should look for the data in master workbook and update the source workbook.

Source workbook is from where the macro will be run.

Note :

As stated earlier both workbooks will be stored in different folders. So option should be provided in the macro to change path of master workbook

 

[Bimmy]

Any assistance will be appreciated...

 

[Marc L]

As a demonstration, you should amend the code to your need …

Master workbook path in source workbook Sheet1 A1 cell :​

Sub Demo()
            If Dir(Sheet1.[A1].Value) = "" Then Beep: Exit Sub
            Application.ScreenUpdating = False
            Sheet1.[F2].CurrentRegion.ClearContents
With GetObject(Sheet1.[A1].Value)
    For R& = 1 To .Worksheets.Count
        With .Worksheets(R)
            Sheet1.Cells(1 + R, 6).Value = .Name
            If .Evaluate("COUNTA(D2:H120)") Then Sheet1.Cells(1 + R, 7).Value = "Yes"
        End With
    Next
            .Close False
End With
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Bimmy]

Thanks Marc L...

I changed below code

Sub Demo()

to

Private Sub Workbook_Open()

So that macro runs every time the source workbook is opened.