Macro question - Copying rows into worksheets based on criteria of a specific column

Yes its possible. Why dont you upload a sample file of your data?

To Attach a File:

1. Click on Go Advanced.
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Hi jpsprack,

I've used your data and created a pivot table using it. See the attached and open it.
Pivot Tables have a feature called "Drill Down" where you can create new sheets of data showing specific records.

If you open the attached and double click on a number in the pivot table, it will create a new sheet of what you want.
Example - Double click on the 1045 next to the AAA. You will then have a new sheet of only AAA acount data.

I hope this is easier than writing VBA. (You can rename the new sheet names to the Account if you need). Why have multiple sheets if they are easy to create like this?

Try this code

Option Explicit

Sub transfer_sheets()
Dim i As Long, lrow As Long
Dim sname As String

With Worksheets("MAIN")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        sname = .Range("B" & i).Value
        If Not Evaluate("ISREF('" & sname & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sname
            .Rows("1:1").Copy Worksheets(sname).Range("A1")
        End If
        .Rows(i).Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next i
End With
End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.