Generating report with VBA
 

I have 3 workbooks with each containing at least 7 worksheets. Clients
signs off their reports once updated i.e. their name in cell B2 of
each worksheet. The problem is currently I find difficult to
establshed who has updated the reports without going into individual
sheet. Can someone help me put together a vb code that generates a
report on which sheet(s) are updated a update and which are not.
Please bote that the sheets name are held in a separate workbook,
range a2:a50.

Many thanks

Generating report with VBA
 

Try this:

Sub SheetUpdate()
Dim aWS As Worksheet
Dim newWkSheet As Worksheet
Dim lrow As Integer

Set newWkSheet = Nothing
On Error Resume Next
Set newWkSheet = Worksheets("Summary")
On Error GoTo 0
If newWkSheet Is Nothing Then
Set newWkSheet = Worksheets.Add
newWkSheet.Move after:=Worksheets(Worksheets.Count)
newWkSheet.Name = "Summary"
newWkSheet.Cells(1, 1).Value = "Worksheet"
newWkSheet.Cells(1, 2).Value = "Updated By"
Else
Set myrange = Cells(2, 1).Resize(Rows.Count - 2, 2)
myrange.ClearContents
End If

lrow = 1
For Each aWS In ActiveWorkbook.Worksheets
If aWS.Name < newWkSheet.Name Then
lrow = lrow + 1
newWkSheet.Cells(lrow, 1) = aWS.Name
newWkSheet.Cells(lrow, 2) = aWS.Range("B2")
End If
Next aWS

End Sub


"Ola2B" wrote:

I have 3 workbooks with each containing at least 7 worksheets. Clients
signs off their reports once updated i.e. their name in cell B2 of
each worksheet. The problem is currently I find difficult to
establshed who has updated the reports without going into individual
sheet. Can someone help me put together a vb code that generates a
report on which sheet(s) are updated a update and which are not.
Please bote that the sheets name are held in a separate workbook,
range a2:a50.

Many thanks