Hello Group,
It would be an add-in for my learing, if you can suggest the code for
finding unique account values from Column "A" using the code so that
Shrinivas doesnt have to copy and paste the unique account values manually
in cell M1.
Thanks,
Anish
On Fri, Jul 15, 2011 at 5:27 PM, Anish Shrivastava <anish....@gmail.com>wrote:
> 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> 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 R**equired **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
>> 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
