How to Create Summary Sheet after processing Active or specific sheets?

Posted by hisham hadi | January 25, 2013 .

Actually this code is for my own reference as a template for future to make my life easier because many people ask me to create summary sheet after processing certain sheets. For example we need to extract or sort specific data from current sheet and place sorted data at new summary sheet.

Option Explicit
Sub CreateSummaryList()
Dim IntSht As Integer
Dim ShtSummary As Worksheet
Dim ShtActive As Worksheet
Dim LngRow As Long, LngLstRow As Long
Dim i As Integer
'To check either user work with summary sheet or not? Yes then exit
If UCase(ActiveSheet.Name) = "SUMMARY" Then
    MsgBox "Sorry! You are trying to work with summary sheet."
    Exit Sub
'To check active sheet empty or not? Empty then exit
ElseIf WorksheetFunction.CountA(Cells) = 0 Then
    MsgBox "Sorry! Active Sheet is empty."
    Exit Sub
End If
i = 1 'Starting Row for summary
Set ShtActive = ActiveSheet 'Current sheet

'To check existance of summary sheet and delete
For IntSht = Sheets.Count To 1 Step -1
    If Sheets(IntSht).Name = "Summary" Then
        Application.DisplayAlerts = False
        Sheets(IntSht).Delete
        Application.DisplayAlerts = True
    End If
Next

'To create new summary sheet
Set ShtSummary = Sheets.Add(After:=ActiveWorkbook.Sheets(Sheets.Count))
ShtSummary.Name = "Summary"

'To work with active sheet and  place new data into summary
ShtActive.Activate
LngLstRow = ShtActive.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For LngRow = 1 To LngLstRow
    If ShtActive.Range("A" & LngRow) <> "" Then
        ShtSummary.Range("A" & i) = ShtActive.Range("A" & LngRow)
        'More code goes here
        i = i + 1
    End If
Next

'To work with Summary sheet
ShtSummary.Activate
LngLstRow = ShtSummary.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For LngRow = 1 To LngLstRow
    If ShtSummary.Range("A" & LngRow) <> "" Then
        'Your code goes here
    End If
Next
Range("A1").Select
End Sub

The above code will:

Confirm current sheet is not summary sheet? Summary then exit
Confirm current sheet empty or not? Empty then exit
Loop all sheet – if found summary sheet then delete (Previous summary sheet)
Create new summary at last sheet
Start working with active sheet
Start working with Summary sheet.

DONE..

If you have any question or project please e-mail to me, I will reply and create for free depending on my free time.
Click Here to E-mail
Or
Click here to get your project done with 5 Dollar
Thanks

Related Articles to insert code into Module:
Learn To Record And Playback Macro
Get To Know VBE (Visual Basic Editor)