I am looking for a way to have excel open and compile separate
worksheets for different values in column A, in a large list of data
(about 6000 lines worth).
There are about 10 possible values for column A, and I need a separate
worksheet for each value, with the rest of the data from that line in
that new worksheet.

Is there an automated way to do this? - At the moment I am reduced to
ordering the worksheet by column A and cutting and pasting the lines for
each section onto new worksheets.

One option in a Pivot Table is to "ShowDetail" of any intersection of
data within the Pivot Table. You could use that function to create all
of your extracted worksheets. For example, a macro something like
this:

Sub Test()
'-------- Create basic pivot table of column A fields
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!A:C").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = False
.RowGrand = False
.AddFields RowFields:="Group"
.PivotFields("Group").Orientation = xlDataField
.PivotFields("Group").PivotItems("(blank)").Visibl e = False
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
'-------- Showdetail of each column A field value
Set ws = ActiveSheet
For iRow = 5 To 500
ws.Activate
If ws.Cells(iRow, 1).Value = "" Then Exit For
ws.Cells(iRow, 2).ShowDetail = True
ActiveSheet.Name = ws.Cells(iRow, 1).Value
Next iRow
End Sub

Another option, especially if you expect your underlying worksheet to
change over time, would be to set up external data queries for each of
the values. Then, all you need to do is refresh the query (or have it
do it itself automatically) whenever you need it to be updated.

Here is a macro I wrote long ago which may be what you need, or adaptable to
your needs:

Sub SplitData()
'Extracts data for multiple entities (customers, brands, ??)
'from a master sheet to separate sheets for each entity.
'Assumptions in the code:
'1. ENTITY NAME/ID IS IN COLUMN A
'2. SHEET HAS HEADINGS IN ROW 1 ONLY
'3. THERE IS A HEADING FOR EVERY COLUMN WITH DATA
'4. DATA IS ALREADY SORTED BY COLUMN A
'5. MASTER DATA SHEET IS ACTIVE WHEN MACRO IS RUN
'----------------------------------------------------------
'To use this macro:
'A) Open this file.
'B) Open the Excel file with the data. Make sure the correct sheet is active.
'C) Run the SplitData macro.
'----------------------------------------------------------
'Declare variables.
Dim CellRef1 As Object, BaseSht As String
Dim a As Integer, x As Integer, MT As Integer
Dim CurrID As String, PrevID As String
Dim EndCol As Integer
'Store the name of the starting sheet
BaseSht$ = ActiveSheet.Name
Range("A2").Activate
a% = ActiveCell.Row
'Assign the first entity ID as PrevID (so have a value to compare).
PrevID$ = ActiveCell.Value
'Find the last data column (with a heading).
EndCol% = Cells(1, Columns.Count).End(xlToLeft).Column
MT% = 0
'Go to second row, first column. Walk down column A and test value of
'every cell. Stop when 100 consecutive empty cells are encountered.
Do While MT% < 100
Set CellRef1 = Cells(a%, 1)
CellRef1.Activate
CellRef1.Select
CurrID$ = CellRef1.Value
'If the current cell is empty, add 1 to MT, the empty cell counter.
If CurrID$ = "" Then
MT% = MT% + 1
Else
'If the current cell is not empty, reset MT. Check if its value
'(CurrID$) is the same as the previous row (PrevID$). If it's not
'the same, copy cols 1 through EndCol% for all the PrevID$ rows (including
'row 1). Paste them onto a new sheet, then return to the original sheet
'(BaseSht$). Delete all the PrevID$ rows (but not row 1). Assign the new
'CurrID$ to PrevID$. Reset a% to 1 (first row. Will then increment it).
MT% = 0
If CurrID$ < PrevID$ Then
Range(Cells(1, 1), Cells(a% - 1, EndCol%)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = Cells(2, 1).Value
Sheets(BaseSht$).Activate
Range(Cells(2, 1), Cells(a% - 1, EndCol%)).Select
Selection.EntireRow.Delete
PrevID$ = CurrID$
a% = 1
End If
End If
a% = a% + 1
Loop
End Sub

Hope this helps,

Hutch

"anthonyberet" wrote:

I am looking for a way to have excel open and compile separate
worksheets for different values in column A, in a large list of data
(about 6000 lines worth).
There are about 10 possible values for column A, and I need a separate
worksheet for each value, with the rest of the data from that line in
that new worksheet.

Is there an automated way to do this? - At the moment I am reduced to
ordering the worksheet by column A and cutting and pasting the lines for
each section onto new worksheets.

Randy Harmelink wrote:
One option in a Pivot Table is to "ShowDetail" of any intersection of
data within the Pivot Table. You could use that function to create all
of your extracted worksheets. For example, a macro something like
this:

Sub Test()
'-------- Create basic pivot table of column A fields
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!A:C").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = False
.RowGrand = False
.AddFields RowFields:="Group"
.PivotFields("Group").Orientation = xlDataField
.PivotFields("Group").PivotItems("(blank)").Visibl e = False
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
'-------- Showdetail of each column A field value
Set ws = ActiveSheet
For iRow = 5 To 500
ws.Activate
If ws.Cells(iRow, 1).Value = "" Then Exit For
ws.Cells(iRow, 2).ShowDetail = True
ActiveSheet.Name = ws.Cells(iRow, 1).Value
Next iRow
End Sub

Thanks for that - Can I just run that in excel and see what it does?
Forgive my ignorance - I need it spelled out to me in words of one
syllable :)