A macro that would...

Posted by IML on May 24, 2001 11:21 AM

I know zero about VBA, so feel free to pass if this is too big a task.

Here is what I have:
I have a large spreadsheet (49C * 39K R)

Here is what I would like:
I'd like to key on Column A which is employee number for about 60 employees. Incidentally, it is already sorted by employee number
I'd like each employee to be put on his/her own worksheet or workbook and have it sorted by 3 columns (E, then G, then AG).
I'd like to include the header row (row1) on each of the individual worksheets.

Pipe dream or possible?

Thanks in advance,
Ian

Posted by Barrie Davidson on May 24, 2001 12:08 PM

Hi Ian, interesting code to write. The following code assumes that your data is in a range named DataTable,the first row contains field names, and your data is in 49 columns.

Hope this is what you need,
Barrie

Sub Extract_Macro()
' Written by Barrie Davidson

Dim UniqueIdRange
Dim CriteriaId
Dim IdNumber As Integer
Dim DataSheet As String

DataSheet = ActiveSheet.Name
UniqueIdRange = "A" & Range("DataTable").Rows.Count
Range("A1:" & UniqueIdRange).AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
Range("A1").End(xlToRight).Offset(0, 1).Range("A1"), Unique:=True
UniqueIdRange = Range("A1").End(xlToRight).Address
UniqueIdRange = UniqueIdRange & ":" & Range("A1").End(xlToRight).End(xlDown).Address
IdNumber = 2
Do Until IdNumber > Range(UniqueIdRange).Rows.Count

CriteriaId = Range(UniqueIdRange).Range("A" & IdNumber).Value
Range("DataTable").Select
Selection.AutoFilter Field:=1, Criteria1:=CriteriaId
Range("A1:AW" & Range("A1").End(xlDown).Row).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets(DataSheet).Select
Range("A1").Select
Selection.AutoFilter Field:=1
IdNumber = IdNumber + 1

Loop

End Sub

Posted by IML on May 24, 2001 12:27 PM

Thanks Barry. I won't be able to test this until the middle of next week, but I'll let you know the results.

Thanks again,
Ian