Split workbook

[erinnicole782]

I have one workbook attached that has a listing of all my supervisors
and their direct reports, I also have attached a blank workbook. I
basically need to take each supervisor and all the information about
their direct reports and send it over to the blank workbook.

The end result would be a seperate workbook for each supervisor.

This is really hard to explain: this is how i have been doing it
manually
In the Main workbook (list all supervisors)

Step 1- Pick a supervisor
Step 2- delete all others
Step 3- File save as Lastname.Firstname
Step 4-close the workbook
Step 5-reopen the main Workbook
Step 6- select the next supervisor

then repete steps 2-6 until a seperate workbook has been created for
each supervisor

I am just looking to write a macro that automates this process, its far
to tedious to do it manually.

Please if ther is anybody who can help, please help....i need it


+-------------------------------------------------------------------+
|Filename: Excel Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4620 |
+-------------------------------------------------------------------+

 

[Ron de Bruin]

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

Without code you can use EasyFilter
http://www.rondebruin.nl/easyfilter.htm

 

[erinnicole782]

Thanks for the reply,

But When I tried that it only copied one column to a new workbook, an
it was a new workbook not the workbook I need it to copy to.

I would like to be able to filter by the supervisor column but the
copy the entire row over to a new workbook that I have already created


in less i am just not doing this right its only copying the uniqu
values in the column and not taking all the other coorespondin
information in that row over with it.

Please let me know if you have any more suggestions

Thanks:

 

[Ron de Bruin]

Hi erinnicole782

Which column is your supervisor column ?
Do you have unique headers in your sheet ?

 

[erinnicole782]

The supervisor Column in in Column J

i have attached the files for you to see if that helps at all.

But yes each column does had a unique column heading

Erin

 

[Ron de Bruin]

Can't see your files because I read this in the newsgroup and no attachments are allowed here.
But try this

Assume that your data start in A1 I change this line to filter on column J (column 10)
rng.Columns(10).AdvancedFilter

Data is in "Sheet1" of your workbook and the new files wil be in C:\Data
If this is not working then send me the files private

Sub Copy_With_AdvancedFilter_To_Workbooks()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WBNew As Workbook
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim FileFolder As String

FileFolder = "C:\Data\" '<<< Change
Set ws1 = ThisWorkbook.Sheets("Sheet1") '<<< Change
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

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

With ws1
'This example filter on the first column in the range (change this if needed)
'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the columns)
rng.Columns(10).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True

Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Set WBNew = Workbooks.Add
On Error Resume Next
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WBNew.Sheets(1).Range("A1"), _
Unique:=False
WBNew.Sheets(1).Columns.AutoFit
WBNew.SaveAs FileFolder & Format(Now, "yyyy-mmm-dd hh-mm-ss") & " Value = " & cell.Value
WBNew.Close False
Next
.Columns("IU:IV").Clear
End With

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

 

[erinnicole782]

I keep getting errors

"out of range" I dont know why

how can i send you the files, so you can see what im talking about? I
really hard to try and explain what im trying to do without actuall
seeing i

 

[Ron de Bruin]

send it to the address on my site

 

[erinnicole782]

I sent you an e mail, just now

thanks

Erin

 

[Ron de Bruin]

Send you a test file back

 

[erinnicole782]

where did you send it to? I have not recieved it yet?

Thanks

Erin

 

[Ron de Bruin]

I reply to your mail