Split worksheets

**Fawkes** · 08-10-2009, 05:21 PM

Hi

I have an excel worksheet with 22 columns. Columns A and B hold user identification and column C through V hold user data for different states. eg. Col C is for AZ, col D for MD and so on. I need to split the state columns into separate worksheets such that every state has it's own worksheet, with the user identification data (col A and B) repeated in each sheet.

So basically, my sheets need to look like this:

Sheet 1 - Col A, B, C
Sheet 2 - Col A, B, D
Sheet 3 - Col A, B, E
...
Sheet 20 - Col A, B, V

Any help will be greatly appreciated.

Thanks!

**JBeaucaire** · 08-10-2009, 05:48 PM

Try this on a copy of your workbook.

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.

**Fawkes** · 08-11-2009, 01:31 PM

Thanks

It works perfectly!

**JBeaucaire** · 08-11-2009, 03:51 PM

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)