Thread: Split worksheets

Try this on a copy of your workbook.

Code:

Sub SplitStates()
'JBeaucaire (8/10/2009)
Dim sh As Worksheet
Dim LR As Long, LC As Long, i As Long, v As Long

Set sh = ActiveSheet
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 3 To LC
    v = Evaluate("ISREF(" & Cells(1, i).Text & "!A1)")
        If v Then       'clear existing sheet
            Sheets(Cells(1, i).Text).Cells.Clear
        Else            'create new sheet
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Cells(1, i)
            sh.Activate
        End If
'Copy Data
    Range("A:B").Copy Sheets(Cells(1, i).Text).Range("A1")
    Columns(i).Copy Sheets(Cells(1, i).Text).Range("C1")

Next i

End Sub

=======
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Thanks


It works perfectly!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)