Macro explanation, cell() and sheets.("sds").select

[newexler]

Hi there

Please would be great to get some explanation of the following macro

Cells(RowCnt, ChkCol).EntireRow.Copy
Range("D65536").End(xlUp).Offset(1, -3).Select
ActiveSheet.Paste

Questions:
1. How to define the range ? what is "D65536". What is Offset and parameters.

2. Why i am not able to do this
Cells(RowCnt, ChkCol).EntireRow.Copy
Range("D65536").End(xlUp).Offset(1,-3).Sheets("Result1").Select
ActiveSheet.Paste

How to break this macro after copying the mentioned range and to ask for a new sheet to be open so that i can paste the content. Is there any better ways to do this.

Any help in this regard would be great.

 

[Hooi]

Hi there

Please would be great to get some explanation of the following macro

Cells(RowCnt, ChkCol).EntireRow.Copy
Range("D65536").End(xlUp).Offset(1, -3).Select
ActiveSheet.Paste

Questions:
1. How to define the range ? what is "D65536". What is Offset and parameters.

2. Why i am not able to do this
Cells(RowCnt, ChkCol).EntireRow.Copy
Range("D65536").End(xlUp).Offset(1,-3).Sheets("Result1").Select
ActiveSheet.Paste

How to break this macro after copying the mentioned range and to ask for a new sheet to be open so that i can paste the content. Is there any better ways to do this.

Any help in this regard would be great.

Question 1
if u putting range("A1") or range("d65536") it will only select 1 cell
if ur range is range("A1:B1") i will auto choose 2 column in 1 row whenever u select everywhere...
for offset(row offset,column offset)
row offset positive is moving down 1 row while negative is moving up
column offset positive is moving to the right while negative is to the left

Question 2
can u explain more on what input u will get and what output u desired to?

 

[newexler]

Thank you very much Hooi,
I have a huge excel sheet with 3-4 columns information i need to sort
Typically like this
1. Sort out all the rows which has "OK" or "ok" (upper or lower case) or may be "Y" or "y" in column x and store in "Result 1"

2. A very similar result but i will run the macro by changing the coloumn numbers for 3 more coloumns

3. I need to run the same macro but this time columns "a", "b", "c", "d" should all have "Y" or "y" or "OK" or "ok", such data needs to go in another sheet

4. Steps 1 to 3 based on the Criteria ( Immediate), ( Important), (Can be deferred).

I am complete beginner so i am collecting information from net here and there to come with a solution. The one i sought help is picked in some forums and i was trying to customize it by a line Sheets("Result1").Select
as very basic attempt atleast to get my question 1 working.

Looks like i need big help ...
Example sheet i prepared is here..

<table x:str="" style="border-collapse: collapse; width: 724pt;" border="0" cellpadding="0" cellspacing="0" width="965"><col style="width: 48pt;" span="10" width="64"> <col style="width: 244pt;" width="325"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">Category</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" x:num="" height="17" width="64">1</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">2</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">3</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">4</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">5</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">6</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">7</td> <td class="xl24" style="width: 48pt;" x:num="" width="64">8</td> <td class="xl24" style="width: 244pt;" width="325">Description</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">I</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 244pt;" width="325">Italy</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 244pt;" width="325">Germany</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">Ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">Uraguay</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">No</td> <td class="xl24" style="width: 48pt;" width="64">Ok</td> <td class="xl24" style="width: 48pt;" width="64">Ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">I</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 244pt;" width="325">Argentina</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">Ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">I</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">Spain</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">I</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 244pt;" width="325">Italy</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">I</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 244pt;" width="325">Italy</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">Ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">Uraguay</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">OK</td> <td class="xl24" style="width: 48pt;" width="64">ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">Ok</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 244pt;" width="325">Uraguay</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="11" rowspan="17" class="xl24" style="height: 216.75pt; width: 724pt;" height="289" width="965">Looking for help here.. In this sheet, I would like to sort out this way
1. If col C,D,E,F are ok, copy the entire row to Result 1
2. If col C,D are Ok and E is not, copy the entire row to Result 2
3. If col C,D,E, are OK and col G is 'I' copy to Result 3
Note: Please give me a generic Macro so that i will copy in the work copy, col positions may change ..
The result should take care of the blank rows in between and length can grow over time</td> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> <tr style="height: 12.75pt;" height="17"> </tr> </tbody></table>
This is a sample sheet .. Any help will be greatly appreciated.
Cheers

 

[newexler]

Could manage some data from advance filter, but any macro to control multiple columns with conditional statement is really appreciated.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Hooi]

Sorry newexler it is out of my capable...

 

[newexler]

Hi Hooi, Thank you for the response and no worries at all. Hopefully some expert will show me the light or will find alternative ways .don't worry at all.
cheers
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">