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)

Actually in Microsoft Excel we have these 2 function index and match when combined it become very powerful formula even better than vlookup function. To understand this formula may be  difficult at the first place but when we use to it sooner or later it become normal. This tutorial actually for me to remember how it work but if you feel useful too then much better. When I try to use vlookup function but fail because data position at reverse side, that why I’m trying to look for other alternative. So, I found this function when Google for solution.

To make the scenario more simple and easy to understand. Let try example below:

1. In Column B3 to B8 – Just numbering 1,2,3,….
2. In Column C3 to C8 we have fruit name
3. In Column D3 to D8 we have quantity

This quantity keep on changing. What we want here is, the Maximum fruit that having the highest quantity. Forget about Visual Basic for Application just use formula. Let say we want the result in F3. Try this formula and insert into Cell F3:

=INDEX(B3:D10,MATCH(MAX(D3:D10),D3:D10,0),2)

The explanation refer below picture.

This definition I get from help for Index function the equation as below:
INDEX(array,row_num,column_num)

Array is a range of cells or an array constant.

• If array contains only one row or column, the corresponding row_num or column_num argument is optional.
• If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.

and for Match equation as below:
MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

• Lookup_value is the value you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.
• Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
• If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
• If match_type is omitted, it is assumed to be 1.

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

When we work with text file in Excel normally what we do either we use open with or import data to place text file data into excel sheets. After that we use text to column function to split text string into column commonly is CSV (Comma delimited) file. This time I don’t want to this way, what I want is to let Excel read text file line by line and place the data into cell one by one. The code below will do the job, when run it’s allow you to browse and and select any text file(Multiple file is allow) and read text file line by line and place this data into cells. Please try and let me know.

For single text file:

Option Explicit
Sub OpenTextFileAndReadLineByLine()
' Requires a reference to Microsoft Scripting Runtime (Tools > References)
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
Dim ActiveWb As Workbook, ActiveWs As Worksheet
Dim OpenWb As Workbook
Dim ShtCount As Integer, Sht As Integer
Dim i As Integer, k As Integer

Dim StrTextLine As String
Dim SplTextLine As Variant

Dim FSO As FileSystemObject
Dim FSOFile As File
Dim FSOStream As TextStream

Set ActiveWb = ActiveWorkbook
Set ActiveWs = ActiveWb.ActiveSheet
i = 1
Filter = "Text Files (*.txt*),*.txt*,All Files (*.*),*.*"
FilterIndex = 1 'Changed this set default open file
Title = "Please select any file to Open"
With Application
    Filename = .GetOpenFilename(Filter, FilterIndex, Title, , False)
End With
If Filename <> "" Then
    Set FSO = New FileSystemObject
    Set FSOFile = FSO.GetFile(Filename)
    Set FSOStream = FSOFile.OpenAsTextStream(ForReading, TristateUseDefault)
    Do While Not FSOStream.AtEndOfStream
        StrTextLine = FSOStream.ReadLine
        SplTextLine = Split(StrTextLine, ",")
        For k = LBound(SplTextLine) To UBound(SplTextLine)
            Cells(i, k + 1) = SplTextLine(k)
        Next
        i = i + 1
    Loop
    ActiveWb.Activate
    ActiveWs.Select
Else
    MsgBox "No file was selected."
End If
End Sub

For Multiple text file:

Option Explicit
Sub OpenMultiTextFileAndReadLineByLine()
' Requires a reference to Microsoft Scripting Runtime (Tools > References)
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
Dim ActiveWb As Workbook, ActiveWs As Worksheet
Dim OpenWb As Workbook
Dim ShtCount As Integer, Sht As Integer
Dim i As Integer, j As Integer, k As Integer

Dim StrTextLine As String
Dim SplTextLine As Variant

Dim FSO As FileSystemObject
Dim FSOFile As File
Dim FSOStream As TextStream

Set ActiveWb = ActiveWorkbook
Set ActiveWs = ActiveWb.ActiveSheet
i = 2
Filter = "Text Files (*.txt*),*.txt*,All Files (*.*),*.*"
FilterIndex = 1 'Changed this set default open file
Title = "Please select any file to Open"
With Application
    Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
End With
If IsArray(Filename) Then
    For j = LBound(Filename) To UBound(Filename)
        Set FSO = New FileSystemObject
        Set FSOFile = FSO.GetFile(Filename(j))
        Set FSOStream = FSOFile.OpenAsTextStream(ForReading, TristateUseDefault)
        Do While Not FSOStream.AtEndOfStream
            StrTextLine = FSOStream.ReadLine
            SplTextLine = Split(StrTextLine, ",")
            For k = LBound(SplTextLine) To UBound(SplTextLine)
                Cells(i, k + 1) = SplTextLine(k)
            Next
            i = i + 1
        Loop
    Next
    ActiveWb.Activate
    ActiveWs.Select
Else
    MsgBox "No file was selected."
End If
End Sub

Note: The above code required reference to Microsoft Scripting Runtime (Unde VBE goto Tools > References and select this option).
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