Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Solver Internal Error When Generating Answer Report | Excel Discussion (Misc queries) | |||
Generating a Weekly Report based on a Date Range, where 1 Column = 1 Day | Excel Programming | |||
Generating a report | Excel Worksheet Functions | |||
Generating a report list and statistic worksheet in Excel from other workbooks | Excel Discussion (Misc queries) | |||
generating report from excel, trouble with bookmark.. | Excel Programming |