Hi Don,
1. How if I want to copy specific columns from ci file to paste on mr and
cy sheet? Need to set range first?
2. What code can be added to make the location to be split into parts? just
like sample shown in mr and cy sheet?
2011/10/29 dguillett1 <dguille...@gmail.com>
> I did this in the ci file after adding mr sheet and cy sheet
>
> Sub CopyMRandCY_SAS()
> Application.Goto Sheets("ci").Range("a1")
> lr = Cells(Rows.Count, 1).End(xlUp).Row
> With Range("A4:as" & lr)
> .AutoFilter Field:=39, Criteria1:="=*mr*"
> .SpecialCells(xlCellTypeVisible).Copy Sheets("mr").Range("a1")
> .AutoFilter
> .AutoFilter Field:=39, Criteria1:="=*cy*"
> .SpecialCells(xlCellTypeVisible).Copy Sheets("cy").Range("a1")
> .AutoFilter
> End With
> End Sub
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
> *From:* Darwin Chan <darwin.chankaw...@gmail.com>
> *Sent:* Friday, October 28, 2011 6:27 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets
> and put into one sheet
>
> Don,
>
> I used your method, however, it is part of my requirement. Next will be
> spliting the data into 2 according to the location of the data.
>
> For example, 3MR3C14015 to be put in worksheet "MR", others put in "CY".
>
> 2011/10/29 dguillett1 <dguille...@gmail.com>
>
>> What you do NOT understand that is if you do it your way, it will be a
>> separate filter and delete rows for each item.
>> You could use a SELECTCASE method with a loop from the bottom up to
>> inspect each cell. Again, slow....
>> I STRONGLY suggest that you adapt my suggested method...
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>> *From:* Darwin Chan <darwin.chankaw...@gmail.com>
>> *Sent:* Friday, October 28, 2011 10:25 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets
>> and put into one sheet
>>
>> Hi Don,
>>
>> it is part of the requirement. however, the "MYLIST" seldom change so I
>> intently hard code into it first. For others, it is too complicate too I
>> think. Please refer to my new file.
>>
>> The requirements are more concrete. Thanks.
>>
>> Darwin
>>
>> 2011/10/28 dguillett1 <dguille...@gmail.com>
>>
>>> Let me see your file(s) after you redo it.
>>>
>>> Don Guillett
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> *From:* Darwin Chan <darwin.chankaw...@gmail.com>
>>> *Sent:* Friday, October 28, 2011 9:55 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ Compare the data across different
>>> sheets and put into one sheet
>>>
>>> Thanks Don,
>>>
>>> Your template gives me more insight on VBA code!
>>>
>>> As I found the whole code is different to reexamine, I revise the
>>> documents into one with more concrete requirement, hope that everyone can
>>> easily understand.
>>>
>>> Thanks again!
>>>
>>> Darwin Chan
>>>
>>> 2011/10/28 dguillett1 <dguille...@gmail.com>
>>>
>>>> If you are talking about the ci file deleting rows based on
>>>> filtering then make a list and NAME it with an automatic defined name such
>>>> as mylist and fill in the list to delete and fire this. See attached file.
>>>> I have done this in the ci file but you can modify to run from the other
>>>> file.
>>>>
>>>> Sub DeleteRowsCol_I_SAS()
>>>> With Sheets("ci")
>>>> lr = .Cells(Rows.Count, 1).End(xlUp).Row
>>>> .Range("I1") = "VAL"
>>>> .Range("I1:I" & lr).AdvancedFilter Action:=xlFilterInPlace, _
>>>> CriteriaRange:=Range("MYLIST"), Unique:=False
>>>> .Rows(2).Resize(lr).Delete
>>>> .Range("i1") = " "
>>>> .ShowAllData
>>>> End With
>>>> End Sub
>>>>
>>>> Don Guillett
>>>> SalesAid Software
>>>> dguille...@gmail.com
>>>>
>>>> *From:* Darwin Chan <darwin.chankaw...@gmail.com>
>>>> *Sent:* Thursday, October 27, 2011 11:07 PM
>>>> *To:* excel-macros@googlegroups.com
>>>> *Subject:* $$Excel-Macros$$ Compare the data across different sheets
>>>> and put into one sheet
>>>>
>>>> Dear all,
>>>>
>>>> I get 1 task of changing the code written by predecessor to comparing
>>>> the data from several sheets.
>>>>
>>>> 1. In the sheets it use several times, I found the original very
>>>> complicated, can anyone suggest to simplify the code?
>>>>
>>>> 2. When execute the procedures, I found some errors, i think it may be
>>>> due to predecessor partly use macro recording.
>>>>
>>>> Detail of how is the logic can be seen from the file.
>>>>
>>>> Thanks in advance!
>>>>
>>>> Darwin
>>>> --
>>>> FORUM RULES (925+ 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 (925+ 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 (925+ 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 (925+ 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
>>>
>>
>>
>>
>> --
>> *Darwin Chan*
>>
>>
>> --
>> FORUM RULES (925+ 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 (925+ 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
>>
>
>
>
> --
> Darwin Chan
> darwin.chankaw...@gmail.com
> kw42c...@yahoo.com.hk
>
> --
> FORUM RULES (925+ 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 (925+ 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
>
--
Darwin Chan
darwin.chankaw...@gmail.com
kw42c...@yahoo.com.hk
--
FORUM RULES (925+ 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
