Hi Amol
I have sometimes solved problems like this, by, rather than copying
part of the workbook, copying the whole workbook and then deleting the
parts i did not want in the copy. I do not know if this approach
would work for you.
Regards
David grugeon
On 26/05/2012, Amol Jadhav <asjad...@gmail.com> wrote:
> Hi Rajan,
>
> Thanks for quick reply
>
> Some part of work is done. Still problems remain same like -- In splitted
> files if you check validation is not working, If you check in main
> file range G:i there is validation and values comes from validation sheet.
> I want same validation in splitted workbooks, so user can select
> appropriate option.
>
> Regards,
> Amol Jadhav
>
> On 26 May 2012 14:54, Rajan_Verma <rajanverma1...@gmail.com> wrote:
>
>> Hi****
>>
>> ** **
>>
>> Try this:****
>>
>> ** **
>>
>> *Sub SplitAllbyCode()*
>>
>> * *
>>
>> * Dim rngRange As Range*
>>
>> * Dim wksSheet As Worksheet*
>>
>> * Dim ArrUniqe*
>>
>> * Dim lngUniqeCount As Long*
>>
>> * Dim wbkNew As Workbook*
>>
>> * *
>>
>> * Set wksSheet = ThisWorkbook.Worksheets("Data")*
>>
>> * With wksSheet*
>>
>> * Set rngRange = Intersect(Range("rngStart").CurrentRegion,
>> Range("rngStart").CurrentRegion.Offset(2))*
>>
>> * Application.ScreenUpdating = False*
>>
>> * rngRange.Columns(6).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 6,
>> 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*
>>
>> * *
>>
>> *See the post on
>> *
>> http://excelpoweruser.blogspot.in/2012/05/split-data-into-multiple-workbook.html
>> ****
>>
>> ** **
>>
>> * *
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>> ** **
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *Amol Jadhav
>> *Sent:* 26 May 2012 2:18
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Copy Validation while splitting workbooks****
>>
>> ** **
>>
>> Hi Experts,****
>>
>> ****
>>
>> I want to make some changes in existing macro, Currently I have macro
>> which creates multiple files according to user and it working fine. But
>> now
>> I want to make some changes****
>>
>> ****
>>
>> If you check attached file, I have created validation and dependent
>> validation in field range G:I (Dependent validation Means if I select
>> "Completed" option from status field, next field will give option
>> accordingly. Using Name range). ****
>>
>> ****
>>
>> What I want is while creating files as per user field, macro will also
>> copy validations****
>>
>> & validation sheet , and will paste it into each workbook****
>>
>> ****
>>
>> PFA****
>>
>> ****
>>
>> Regards,****
>>
>> Amol****
>>
>> ****
>>
>> ****
>>
>> --
>> 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
>>
>> To unsubscribe, send a blank email to
>> excel-macros+unsubscr...@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
>>
>> To unsubscribe, send a blank email to
>> excel-macros+unsubscr...@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
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@googlegroups.com
--
David Grugeon
--
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
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
