Split excel in to several sheets based on column data?

While working on yearly reports or data you have for several users. You have the consolidated data in to several sheets based on year or user names. This can be easily achieved by macro. Please find below an example and a macro to achieve this.

Suppose you have a table as below which has name and count. You want to split the data in to several sheets based on name. Press Alt+F11. Insert module and then paste the below macro code.

Below macro will help you to achieve this.

-----------------------------------------------------------------------

Sub splitToSheet()

Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long

Dim ws As Worksheet

Application.ScreenUpdating = False

With ActiveSheet

    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _

        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    iStart = 2

    For i = 2 To lastrow

        If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then

            iEnd = i

            Sheets.Add after:=Sheets(Sheets.Count)

            Set ws = ActiveSheet

            On Error Resume Next

            ws.Name = .Range("A" & iStart).Value

            On Error GoTo 0

            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value

            With ws.Rows(1)

                .HorizontalAlignment = xlCenter

                With .Font

                    .ColorIndex = 5

                    .Bold = True

                End With

            End With

            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")

            iStart = iEnd + 1

        End If

    Next i

End With

Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

-----------------------------------------------------------------------------

Specify a shortcut to macro.

Steps to specify a shortcut.

1. Under Developer tab click on "Macro". click on "Option" and supply shortcut key.

You can save the above file as excel addins and later can add the addins to any excel file for re use.

1. Select Save as Option

2. Select save as type "Excel Add-in"

3. Type a name for addins and provide location. For this macro i will use name "Splitsheet"

4. Select Excel options after clicking on Window Balloon on top left corner.

Next step is to click on "Add-Ins" and click "Go" button after Manage - Excel Addins Option.

Click on "Browse" and select the addins from the location where you have saved it. Thats it you can reuse the macro in any file. You just have to run the shortcut key you have provided to macro.