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
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
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
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
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
