This is a a better approach than looping
Filter>copy visible to new workbook>save as >close
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Rajan_Verma
Sent: Thursday, May 03, 2012 8:27 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Split file individually -Need macro
Hi
Try the Blow Code to Split Date with in multiple workbook
Please find the attached File
Sub SplitAllbyCode()
Dim rngRange As Range
Dim wksSheet As Worksheet
Dim ArrUniqe
Dim lngUniqeCount As Long
Dim wbkNew As Workbook
Set wksSheet = ThisWorkbook.Worksheets("Sheet1")
With wksSheet
Set rngRange = Intersect(Range("rngStart").CurrentRegion,
Range("rngStart").CurrentRegion.Offset(2))
Application.ScreenUpdating = False
rngRange.Columns(4).Copy Range("rngRemoveDuplicate")
Range("rngRemoveDuplicate").CurrentRegion.RemoveDuplicates 1
ArrUniqe = Range("rngRemoveDuplicate").CurrentRegion
For lngUniqeCount = LBound(ArrUniqe) To UBound(ArrUniqe)
Range("rngStart").CurrentRegion.Rows(2).AutoFilter 4,
ArrUniqe(lngUniqeCount, 1)
Range("rngStart").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Set wbkNew = Workbooks.Add
wbkNew.Worksheets(1).Paste
wbkNew.SaveAs ThisWorkbook.Path & "\" & ArrUniqe(lngUniqeCount, 1)
wbkNew.Close 1
Next lngUniqeCount
End With
wksSheet.AutoFilterMode = False
Application.ScreenUpdating = True
Range("rngRemoveDuplicate").CurrentRegion.ClearContents
MsgBox lngUniqeCount & " Files has been splited, Plase find your files
at " & vbCrLf & ThisWorkbook.Path
'Free Memory
Set rngRange = Nothing
Set wksSheet = Nothing
Erase ArrUniqe
Set wbkNew = Nothing
End Sub
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of NOORAIN ANSARI
Sent: 03 May 2012 15:12
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Split file individually -Need macro
Dear Murali,
Please see attached sheet,
Hope it will useful to you.
Kindly change path in the code then run.
Wait 5 minute after run the program.
--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com
On Thu, May 3, 2012 at 2:19 PM, MURALI NAGARAJAN <muralin...@gmail.com> wrote:
Dear experts.
We want to split the data individually and the same file should save
individually as"code name" in new folder(Desktop/My document).
Thanks in advance.
Thanks@Regards
Murali.N
Chennai
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
