This copies only columns shown
Sub CopyMRandCYSAS()
Application.Goto Sheets("ci").Range("a1")
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Range("A4:as" & lr)
.AutoFilter Field:=39, Criteria1:="=*mr*"
Range("a1:b1,e1,h1:i1").EntireColumn.SpecialCells(xlCellTypeVisible) _
.Copy Sheets("mr").Range("a1")
.AutoFilter
.AutoFilter Field:=39, Criteria1:="=*cy*"
Range("a1:b1,e1,h1:i1").EntireColumn.SpecialCells(xlCellTypeVisible) _
.Copy Sheets("cy").Range("a1")
.AutoFilter
End With
End Sub
Don Guillett
SalesAid Software
dguille...@gmail.com
From: Darwin Chan
Sent: Sunday, October 30, 2011 9:15 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Compare the data across different sheets and put
into one sheet
1) Refer to picture, not all the columns from ci file need to paste to cy and
mr sheets, just for example the highlight field, I have mentioned this in the
file too.
2) for example location from ci file 3MR3C14015, need to separate and put as
follows. 3-->Zone, MR-->Street , 3C--> Stack, 14--> Bay,01--> Row , 5--> Tier
2011/10/30 dguillett1 <dguille...@gmail.com>
A better explanation
Don Guillett
SalesAid Software
dguille...@gmail.com
From: Darwin Chan
Sent: Sunday, October 30, 2011 4:28 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Compare the data across different sheets and
put into one sheet
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
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
--
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
--
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
