Thank a lot Anish, It is working fine.
________________________________ From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Anish Shrivastava Sent: Friday, July 15, 2011 5:28 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Multiple excel files Hi Shrinivas, Attached please find the Dump file with macro. You will have to copy and paste your unique account numbers in Cell "M1" before running the macro. Data will be splitted based on the account numbers and new files will be saved with account names. Also note that I have done it with Excel 2007, if you dont have this version, please let me know so that I can make it for 2003. Let me know if you face any issues with this macro. Below is the code for your ref. ------------------------------------------------------------------------------------------------------------------------------------- Sub split_data() Dim lst_row, lst_col, i, j As Integer Dim ws_name As String Dim nwkb As Workbook Dim ac_num As String Dim uname As String Sheets(1).Rows("1").Select Selection.Insert shift:=xlDown lst_row = Sheets(1).Range("A2").End(xlDown).Row lst_col = Sheets(1).Range("A3").End(xlToRight).Column Range("A1", Cells(1, lst_col)).Select Selection.AutoFilter For j = 2 To Sheets(1).Range("M2").End(xlDown).Row ac_num = Sheets(1).Cells(j, "M").Value uname = Environ("Username") ActiveSheet.Range("A1", Cells(lst_row, lst_col)).AutoFilter Field:=1, Criteria1:=ac_num For i = 2 To 1000 ' change to your ranges If Not Rows(i).Hidden Then theFirstUnhiddenRowinArea = i Range("B" & i).Select Exit For End If Next Range(Selection, Cells(lst_row, lst_col)).Select Selection.Copy Set nwkb = Workbooks.Add nwkb.Activate ActiveSheet.Paste nwkb.SaveAs "C:\Users\" & uname & "\Desktop\" & ac_num & ".xlsx", FileFormat:=xlOpenXMLWorkbook nwkb.Close Next j Sheets(1).Rows("1").Delete shift:=xlUp End Sub ---------------------------------------------------------------------------------------------------------------------------------------------- Thanks, Anish On Fri, Jul 15, 2011 at 4:12 PM, Chidurala, Shrinivas <shrinivas.chidur...@citi.com<mailto:shrinivas.chidur...@citi.com>> wrote: Dear Friends, Please help me create macro for splitting the dump file into multiple excel files as per account number in column A and file name should be account no. I have using the attached code for splitting the different dump file but it is not working in this dump file. Please advise on the same. Find attached sample of dump file , required format and code. Dump file Required file Thanking in advance. Regards, Shrinivas -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com<http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com<http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com<http://exceldailytip.blogspot.com/> To post to this group, send email to excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
