|
Split workbook
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 | +-------------------------------------------------------------------+ -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
See
http://www.rondebruin.nl/copy5.htm Without code you can use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "erinnicole782" wrote in message news:erinnicole782.265nro_1144851903.7037@excelfor um-nospam.com... 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 | +-------------------------------------------------------------------+ -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
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: -- erinnicole78 ----------------------------------------------------------------------- erinnicole782's Profile: http://www.excelforum.com/member.php...fo&userid=3340 View this thread: http://www.excelforum.com/showthread.php?threadid=53229 |
Split workbook
Hi erinnicole782
Which column is your supervisor column ? Do you have unique headers in your sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "erinnicole782" wrote in message news:erinnicole782.265tjy_1144859401.6058@excelfor um-nospam.com... Thanks for the reply, But When I tried that it only copied one column to a new workbook, and 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 then 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 unique values in the column and not taking all the other cooresponding information in that row over with it. Please let me know if you have any more suggestions Thanks:( -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
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 -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
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 -- Regards Ron de Bruin http://www.rondebruin.nl "erinnicole782" wrote in message news:erinnicole782.265x0y_1144863901.6305@excelfor um-nospam.com... 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 -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
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 -- erinnicole78 ----------------------------------------------------------------------- erinnicole782's Profile: http://www.excelforum.com/member.php...fo&userid=3340 View this thread: http://www.excelforum.com/showthread.php?threadid=53229 |
Split workbook
send it to the address on my site
-- Regards Ron de Bruin http://www.rondebruin.nl "erinnicole782" wrote in message news:erinnicole782.265yf0_1144865707.052@excelforu m-nospam.com... 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? It really hard to try and explain what im trying to do without actually seeing it -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
I sent you an e mail, just now thanks Erin -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
Send you a test file back
-- Regards Ron de Bruin http://www.rondebruin.nl "erinnicole782" wrote in message news:erinnicole782.2660hy_1144868401.6116@excelfor um-nospam.com... I sent you an e mail, just now thanks Erin -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
where did you send it to? I have not recieved it yet? Thanks Erin -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
Split workbook
I reply to your mail
-- Regards Ron de Bruin http://www.rondebruin.nl "erinnicole782" wrote in message news:erinnicole782.26624b_1144870502.7796@excelfor um-nospam.com... where did you send it to? I have not recieved it yet? Thanks Erin -- erinnicole782 ------------------------------------------------------------------------ erinnicole782's Profile: http://www.excelforum.com/member.php...o&userid=33406 View this thread: http://www.excelforum.com/showthread...hreadid=532296 |
| All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com