Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

Ron,

Thanks for the help. Yes, it is in 1 sheet. The sheet name is sheet1. The
document name is Ghost Audit and is saved on my Desktop. The first column is
A and the header is Supervisor. The last column is F. If I could just save
them to my Desktop, that would be fine. Anywhere else would work as well.
Again, thanks so much for your help.

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

Hi Aleisha

1: Open Ghost Audit.xls
2: Press Alt F11 to open the VBA editor
3: Use InsertModule in the menu bar
4: Copy/Paste the macro below in this module
5: Alt q to go back to Excel
6: Alt F8 to open the macro dialog
7: Select "Copy_With_AdvancedFilter_To_Workbooks_New"
8: Click on Run

It will filter on column A this example (Supervisor column)
I assume that your headers are in row 1 so the header "Supervisor" is in A1


Sub Copy_With_AdvancedFilter_To_Workbooks_New()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer

'Name of the sheet with your data
Set ws1 = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, F is the last column in the filter range
Set rng = ws1.Range("A1:F" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat
WSNew.Parent.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

'Delete the ws2 sheet
On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

MsgBox "Look in " & foldername & " for the files"

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help. Yes, it is in 1 sheet. The sheet name is sheet1. The
document name is Ghost Audit and is saved on my Desktop. The first column is
A and the header is Supervisor. The last column is F. If I could just save
them to my Desktop, that would be fine. Anywhere else would work as well.
Again, thanks so much for your help.

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

Ron,

You are a genius. This worked perfectly. Thank you for your help on this
one. Have a great day!

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

1: Open Ghost Audit.xls
2: Press Alt F11 to open the VBA editor
3: Use InsertModule in the menu bar
4: Copy/Paste the macro below in this module
5: Alt q to go back to Excel
6: Alt F8 to open the macro dialog
7: Select "Copy_With_AdvancedFilter_To_Workbooks_New"
8: Click on Run

It will filter on column A this example (Supervisor column)
I assume that your headers are in row 1 so the header "Supervisor" is in A1


Sub Copy_With_AdvancedFilter_To_Workbooks_New()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer

'Name of the sheet with your data
Set ws1 = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, F is the last column in the filter range
Set rng = ws1.Range("A1:F" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat
WSNew.Parent.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

'Delete the ws2 sheet
On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

MsgBox "Look in " & foldername & " for the files"

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help. Yes, it is in 1 sheet. The sheet name is sheet1. The
document name is Ghost Audit and is saved on my Desktop. The first column is
A and the header is Supervisor. The last column is F. If I could just save
them to my Desktop, that would be fine. Anywhere else would work as well.
Again, thanks so much for your help.

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

You are welcome

If you want we can make a macro to mail the 120 workbooks also.
Let me know if you want that



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

You are a genius. This worked perfectly. Thank you for your help on this
one. Have a great day!

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

1: Open Ghost Audit.xls
2: Press Alt F11 to open the VBA editor
3: Use InsertModule in the menu bar
4: Copy/Paste the macro below in this module
5: Alt q to go back to Excel
6: Alt F8 to open the macro dialog
7: Select "Copy_With_AdvancedFilter_To_Workbooks_New"
8: Click on Run

It will filter on column A this example (Supervisor column)
I assume that your headers are in row 1 so the header "Supervisor" is in A1


Sub Copy_With_AdvancedFilter_To_Workbooks_New()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer

'Name of the sheet with your data
Set ws1 = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, F is the last column in the filter range
Set rng = ws1.Range("A1:F" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat
WSNew.Parent.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

'Delete the ws2 sheet
On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

MsgBox "Look in " & foldername & " for the files"

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help. Yes, it is in 1 sheet. The sheet name is sheet1. The
document name is Ghost Audit and is saved on my Desktop. The first column is
A and the header is Supervisor. The last column is F. If I could just save
them to my Desktop, that would be fine. Anywhere else would work as well.
Again, thanks so much for your help.

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

I would be very interested in knowing how to do that. I use Oulook for my
e-mail, so let me know what i need to do. Thanks.

"Ron de Bruin" wrote:

You are welcome

If you want we can make a macro to mail the 120 workbooks also.
Let me know if you want that



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

You are a genius. This worked perfectly. Thank you for your help on this
one. Have a great day!

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

1: Open Ghost Audit.xls
2: Press Alt F11 to open the VBA editor
3: Use InsertModule in the menu bar
4: Copy/Paste the macro below in this module
5: Alt q to go back to Excel
6: Alt F8 to open the macro dialog
7: Select "Copy_With_AdvancedFilter_To_Workbooks_New"
8: Click on Run

It will filter on column A this example (Supervisor column)
I assume that your headers are in row 1 so the header "Supervisor" is in A1


Sub Copy_With_AdvancedFilter_To_Workbooks_New()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer

'Name of the sheet with your data
Set ws1 = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, F is the last column in the filter range
Set rng = ws1.Range("A1:F" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat
WSNew.Parent.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

'Delete the ws2 sheet
On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

MsgBox "Look in " & foldername & " for the files"

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help. Yes, it is in 1 sheet. The sheet name is sheet1. The
document name is Ghost Audit and is saved on my Desktop. The first column is
A and the header is Supervisor. The last column is F. If I could just save
them to my Desktop, that would be fine. Anywhere else would work as well.
Again, thanks so much for your help.

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.

The basic macro you can find here
http://www.rondebruin.nl/mail/folder2/files.htm

Try it with a few files

We can adapt the other macro to also make a sheet with all the 120 file names but you must enter the mail addresses
manual. Or can we find the mail address in the data ?

Late here (12:07) so I will reply tomorrow if you want to know more


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
I would be very interested in knowing how to do that. I use Oulook for my
e-mail, so let me know what i need to do. Thanks.

"Ron de Bruin" wrote:

You are welcome

If you want we can make a macro to mail the 120 workbooks also.
Let me know if you want that



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

You are a genius. This worked perfectly. Thank you for your help on this
one. Have a great day!

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

1: Open Ghost Audit.xls
2: Press Alt F11 to open the VBA editor
3: Use InsertModule in the menu bar
4: Copy/Paste the macro below in this module
5: Alt q to go back to Excel
6: Alt F8 to open the macro dialog
7: Select "Copy_With_AdvancedFilter_To_Workbooks_New"
8: Click on Run

It will filter on column A this example (Supervisor column)
I assume that your headers are in row 1 so the header "Supervisor" is in A1


Sub Copy_With_AdvancedFilter_To_Workbooks_New()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer

'Name of the sheet with your data
Set ws1 = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, F is the last column in the filter range
Set rng = ws1.Range("A1:F" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat
WSNew.Parent.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

'Delete the ws2 sheet
On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

MsgBox "Look in " & foldername & " for the files"

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help. Yes, it is in 1 sheet. The sheet name is sheet1. The
document name is Ghost Audit and is saved on my Desktop. The first column is
A and the header is Supervisor. The last column is F. If I could just save
them to my Desktop, that would be fine. Anywhere else would work as well.
Again, thanks so much for your help.

Aleisha Mollen

"Ron de Bruin" wrote:

Hi Aleisha

If you want to create a seperate workbooks for 120 people then code is the only way if you want it fast

You have one sheet with all the data and one column is the column with the supervisor names
Correct ?

What is the name of the sheet with your data ?
What is cell with the header of the first column and what is the last column in your data table ?

Give more information and we can try to help you




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Ron,

Thanks for the help, but I don't know much about Visual Basic and I cannot
get this to work. Is there an easier way to do this? I tried downloading the
Easy filter but that doesn't give me the results I need either. Thanks.

Aleisha

"Ron de Bruin" wrote:

Hi Aleisha

See
http://www.rondebruin.nl/copy5.htm

I have mail code also on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Aleisha Mollen" wrote in message
...
Hello. Every 3 months I have to run a report at work that lists all
employees, their title, department, and supervisor. I then have to separate
it into separate workbooks by supervisor and e-mail it out. I am looking for
an easier way to separate it into different workbooks by supervisor name.
Does anyone have any ideas on this? The report usually includes about 120
supervisors for 1800 employees. Thanks.