Macro to tell which sheet in master workbook is empty

Closed
bimmy80 Posts 9 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015 - Nov 19, 2015 at 12:15 PM
Hello,

I will be grateful if someone can help me with below code.

The code -

Sub Demo()
Application.ScreenUpdating = False
Sheet1.[F2].CurrentRegion.ClearContents
For R& = 2 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

What the above macro does -

I have a source workbook with sheets named as 1,2,3 and so on. On sheet 1 all these sheets are listed one below the other from cell F2 onwards. From cell G2 onwards macro updates each cell with Yes if it finds any data in corresponding sheets mentioned in F column. If macro does not find any data it leaves the cells as blank. Macro looks for data in range D2:H120.

F2--- 2--- Yes--- Macro updates Yes if it finds data in sheet 2 in range D2:H120
F3--- 3--- --- Macro leaves the cell blank if no data in sheet 3 in range D2:H120
F4--- 4--- --- Macro leaves the cell blank if no data in sheet 4 in range D2:H120
F5--- 5--- Yes--- Macro updates Yes if it finds data in sheet 5 in range D2:H120

I'm looking for an addition to the above code as below -

Instead of looking for data in source workbook, I want the macro to look for data in master workbook and update the status in source workbook.

Very important -

I would prefer that the macro runs the code when the source workbook is opened.

Note -

1) Source and master workbook will be stored in 2 different folders.
2) Macro will be run from source workbook
3) Master workbook will remain closed
4) Option should be provided in the code to change path of master workbook