Dear Kartik please use below codeand Change red color path
Sub Sheet_Creater_Deptwise()
Dim rng As Range
Dim rng1 As Range
Set rng = Sheet1.Range("G1:G" & Sheet1.Range("G65536").End(xlUp).Row)
rng.Copy Sheet1.Range("H1")
Set rng1 = Sheet1.Range("H1:H" & Sheet1.Range("H65536").End(xlUp).Row)
rng1.Select
Selection.RemoveDuplicates 1
For i = 2 To Sheet1.Cells(Rows.Count, "H").End(xlUp).Row
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Cells(i, "H")
Sheet1.Range("A1:G1").Copy Sheets(Sheets.Count).Range("A1")
c = 2
For j = 2 To Sheet1.Cells(Rows.Count, "G").End(xlUp).Row
If Sheet1.Cells(j, "G").Value = Sheet1.Cells(i, "H").Value Then
Sheets(Sheets.Count).Cells(c, 1).Value = Sheets(1).Cells(j, 1).Value
Sheets(Sheets.Count).Cells(c, 2).Value = Sheets(1).Cells(j, 2).Value
Sheets(Sheets.Count).Cells(c, 3).Value = Sheets(1).Cells(j, 3).Value
Sheets(Sheets.Count).Cells(c, 4).Value = Sheets(1).Cells(j, 4).Value
Sheets(Sheets.Count).Cells(c, 5).Value = Sheets(1).Cells(j, 5).Value
Sheets(Sheets.Count).Cells(c, 6).Value = Sheets(1).Cells(j, 6).Value
Sheets(Sheets.Count).Cells(c, 7).Value = Sheets(1).Cells(j, 7).Value
c = c + 1
End If
Next
Sheets(Sheets.Count).Columns.AutoFit
Next
Sheet1.Range("H:H").ClearContents
End Sub
and
Sub Workbook_Create_Deptwise()
Dim rng As Range
Dim rng1 As Range
Set rng = Sheet1.Range("G1:G" & Sheet1.Range("G65536").End(xlUp).Row)
rng.Copy Sheet1.Range("H1")
Set rng1 = Sheet1.Range("H1:H" & Sheet1.Range("H65536").End(xlUp).Row)
rng1.Select
Selection.RemoveDuplicates 1
For i = 2 To Sheet1.Cells(Rows.Count, "H").End(xlUp).Row
Workbooks.Add
ActiveWorkbook.SaveAs "D:\Noorain\" & Sheet1.Cells(i, "H") & ".xls"
Sheet1.Range("A1:G1").Copy ActiveWorkbook.Sheets(1).Range("A1")
c = 2
For j = 2 To Sheet1.Cells(Rows.Count, "G").End(xlUp).Row
If Sheet1.Cells(j, "G").Value = Sheet1.Cells(i, "H").Value Then
ActiveWorkbook.Sheets(1).Cells(c, 1).Value = Sheet1.Cells(j, 1).Value
ActiveWorkbook.Sheets(1).Cells(c, 2).Value = Sheet1.Cells(j, 2).Value
ActiveWorkbook.Sheets(1).Cells(c, 3).Value = Sheet1.Cells(j, 3).Value
ActiveWorkbook.Sheets(1).Cells(c, 4).Value = Sheet1.Cells(j, 4).Value
ActiveWorkbook.Sheets(1).Cells(c, 5).Value = Sheet1.Cells(j, 5).Value
ActiveWorkbook.Sheets(1).Cells(c, 6).Value = Sheet1.Cells(j, 6).Value
ActiveWorkbook.Sheets(1).Cells(c, 7).Value = Sheet1.Cells(j, 7).Value
c = c + 1
End If
Next
ActiveWorkbook.Sheets(1).Columns.AutoFit
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
Sheet1.Range("H:H").ClearContents
End Sub
Please attached sheet.
--
Thanks & regards,
Noorain Ansari
*http://noorainansari.com/*
*http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
On Wed, Mar 7, 2012 at 3:13 PM, karthik N <n.karthi...@gmail.com> wrote:
> FYI.
>
> Regards
> karthik.N
>
>
> On Wed, Mar 7, 2012 at 1:05 PM, NOORAIN ANSARI
> <noorain.ans...@gmail.com>wrote:
>
>> Dear Kartik,
>>
>> Can you share sample workbook.
>>
>> On Tue, Mar 6, 2012 at 6:35 PM, karthik N <n.karthi...@gmail.com> wrote:
>>
>>> Kindly help me
>>>
>>> To allocate the data in macro branch wise and every branch shouid be
>>> save in new book ,
>>>
>>>
>>>
>>>
>>> Regards
>>> Karthik
>>>
>>> --
>>> 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
>>
>
>
>
> --
>
> *Regards*
>
> --
> 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
>
--
Thanks & regards,
Noorain Ansari
** <http://excelmacroworld.blogspot.com/>*http://noorainansari.com/*
*http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.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
Copy of HELP1(Solved_Noorain).xlsm
Description: Binary data
