Copy\Paste multiple criteria based rows to new sheet

Hi All,

I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.

So from attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5. ??

I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.

Any HELP , ADVICE or a code for this ..plzzzz??

See the attached screenshot to see my main worksheet from where all this data is coming from...

Thnkz.

HI

Can you please attach a sample workbook rather than a gif. If the columns all have headings, it may be possible to use an advanced filter to do this.


rylo

Originally Posted by rylo

HI

Can you please attach a sample workbook rather than a gif. If the columns all have headings, it may be possible to use an advanced filter to do this.


rylo

Okies..Sample worksheet attached hereby.
Thats where data is coming from ..

thnkz.

Hi

This assumes that you have an existing sheet called BigPond. Part of the process will delete any existing data on this sheet.

Nothing will be copied across from your sample sheet as you don't have an entry in column M with the data RG2.

rylo

Please Login or Register  to view this content.

Hi

See how this goes. There is some formatting that hasn't been done, but I'm after the data extract and formula correctness.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

See how this goes. There is some formatting that hasn't been done, but I'm after the data extract and formula correctness.

Please Login or Register  to view this content.

rylo

Hey mate , u r genius.....may b small adjustment should fix this...this is what i got..(see attached screenshot)....last 2 colums are meant to appear under "Externals"...i m sure this shud be a small correction of code...can u have a quick look & let me know..plzzz...

thnkz a ton..

Hi

Do you mean the last 2 rows not columns? I've put in a heading that totals the last 2 rows and calls them external. Do you want a heading to specify these, and perhaps remove the first 2 rows which are used in the extract.

rylo

Originally Posted by rylo

Hi

Do you mean the last 2 rows not columns? I've put in a heading that totals the last 2 rows and calls them external. Do you want a heading to specify these, and perhaps remove the first 2 rows which are used in the extract.

rylo

Sorry didnt got what u explained..but Yes last 2 columns ..one for SURREY HILLS & BIGGERA WATERS are external sites so they should appear under Externals...can this be accomplished plzz..??

Hi

OK, try this.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

OK, try this.

Please Login or Register  to view this content.

rylo

WOW..looks real good to me..all correct info is getting populated...now i've 2 questions for you...

1. can formatting be also fixed using some code in same macro , what i mean i have attached sceernshot "BP" that i got when i ran this macro but i would like formatting to be like shown in screenshot "BP(formated)" . Not major things to fix....."Internals" section looks just perfect..External sections needs to be fixed... I guess if we use "paste - values" instead of simple that should also assist me in future.

2. 2nd thing, now this macro was for 1 client "BigPond" , i have around 2000 rows of colum in my complete extract and it has 12 other clients other than "BigPond" . Can you plz inform me if this same maco code can be used for other clients ? If yes ..than when i copy this code what things should i be change so that i get same result for other Clients.


Thanks buddy....u seems to have solved lot of my issues ...it has been awesome so far...thank again in adv...

Cheers!!

Originally Posted by namz

WOW..looks real good to me..all correct info is getting populated...now i've 2 questions for you...

1. can formatting be also fixed using some code in same macro , what i mean i have attached sceernshot "BP" that i got when i ran this macro but i would like formatting to be like shown in screenshot "BP(formated)" . Not major things to fix....."Internals" section looks just perfect..External sections needs to be fixed... I guess if we use "paste - values" instead of simple that should also assist me in future.

2. 2nd thing, now this macro was for 1 client "BigPond" , i have around 2000 rows of colum in my complete extract and it has 12 other clients other than "BigPond" . Can you plz inform me if this same maco code can be used for other clients ? If yes ..than when i copy this code what things should i be change so that i get same result for other Clients.


Thanks buddy....u seems to have solved lot of my issues ...it has been awesome so far...thank again in adv...

Cheers!!

Screenshots attached..

Hi

1) I think I have the formatting right...

Please Login or Register  to view this content.

2) Yep, just change the value of "Bigpond" in the code to whatever you want to extract.


rylo

Hi

Slightly different approach.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

Slightly different approach.

Please Login or Register  to view this content.

rylo

Nah...did not worked...see screenshot of what i got with this...
it missed all external sites , new BigPond RG2 colums that i created to test and formatting is same...i guess last approach was very close to what we need..

Hi

LRFA has 0 items that match the criteria for external.

If this is not the output format required, then can you provide an example of how it should look.

Actually, can you give example formats for all possible combinations of outputs. eg 1 int, 0 ext; >1 int,0 ext; 0 int,1 ext; 0 int > 1 ext; >1 int, >1 ext; 1 int, 1 ext; >1 int; >1 ext; 0 int, 0 ext.

rylo

Originally Posted by rylo

Hi

LRFA has 0 items that match the criteria for external.

If this is not the output format required, then can you provide an example of how it should look.

Actually, can you give example formats for all possible combinations of outputs. eg 1 int, 0 ext; >1 int,0 ext; 0 int,1 ext; 0 int > 1 ext; >1 int, >1 ext; 1 int, 1 ext; >1 int; >1 ext; 0 int, 0 ext.

rylo

Hey rylo..code ran correct...there was a small issue in your code , it was pulling info for "lrfa" but was pasting it in "bigpond" sheet...that why i got confused..

now whats happening in my excel sheet, i just noticed that i've a client called "CSS" and has a additinal criteria which is in column V (called Group By) & all other cretia like INT\EXT , RG2 remains same....So this BU - CSS is subdivided into small groups as specified in Column V and now we have to extract data for CSS groupx ex. CSS-TDN into CSS-TDN sheet or CSS-PSales into CSS-Psales sheet..

i am attaching sample sheet for your refernce.let me know what changes wud be needed in code to get a code for this situation...or if u can provide a code for this that wud be awesome....



Cheers!!

Hi

Had problems with the attachment. Can you please reload.

rylo

Originally Posted by rylo

Hi

Had problems with the attachment. Can you please reload.

rylo

unable to reload ,its more than 100KBs...any chance i can email u...if yes..than u can PM ur email id..

cheers!!

Hi

How about one of the free online storage facilities. Then provide details on how to get the file (location, password etc)

rylo

Originally Posted by rylo

Hi

How about one of the free online storage facilities. Then provide details on how to get the file (location, password etc)

rylo

het matey..just PMed you...check it out...
cheers!!

Hi

This is specific to the format of the example file.

It produces a result that (a) is pretty bad format wise, and (b) has circular references in the formulas. Don't worry about that for the moment, I'm more interested in the data being returned properly to the correct sheet.

How do you want the output to look when there is no data for INT? In one of the posts, I requested your output structure / format for a variety of results. May help if you put that sample up, as well as how the formulas would work. Maybe run the macro below on the sample file, then show on the same sheet how the output should look, including the formulas.

Run Main (below) to get things going.

rylo

Please Login or Register  to view this content.

Originally Posted by rylo

Hi

This is specific to the format of the example file.

It produces a result that (a) is pretty bad format wise, and (b) has circular references in the formulas. Don't worry about that for the moment, I'm more interested in the data being returned properly to the correct sheet.

How do you want the output to look when there is no data for INT? In one of the posts, I requested your output structure / format for a variety of results. May help if you put that sample up, as well as how the formulas would work. Maybe run the macro below on the sample file, then show on the same sheet how the output should look, including the formulas.

Run Main (below) to get things going.

rylo

Please Login or Register  to view this content.

Hi rylo,

tried this macro but got incorrect results...we just need RG non 0,non blank sites to populate , currently this macro is doing more than what it supposed to pull..

for ex , check CSS-CRG sheet , it shud only display 4-5 Internal sites but macro is getting some external sites which do not belong to CSS-CRG group.

Format is also shown in CSS-CRG sheet , i need this type of format i.e. dotted cells and a thick outline ..for both Internals & Externals sites for all cleinst...Formulas are also correct currently in this sheet...
and if there is no data for INT or EXT , shud appear as shown in CSS-Psales sheet...

CHECK EMAIL FOR SAMPLE SHEET RESULTS...CHeers...

Hi

Check the hotmail address.

rylo

Originally Posted by namz

Hi rylo,

tried this macro but got incorrect results...we just need RG non 0,non blank sites to populate , currently this macro is doing more than what it supposed to pull..

for ex , check CSS-CRG sheet , it shud only display 4-5 Internal sites but macro is getting some external sites which do not belong to CSS-CRG group.

Format is also shown in CSS-CRG sheet , i need this type of format i.e. dotted cells and a thick outline ..for both Internals & Externals sites for all cleinst...Formulas are also correct currently in this sheet...
and if there is no data for INT or EXT , shud appear as shown in CSS-Psales sheet...

CHECK EMAIL FOR SAMPLE SHEET RESULTS...CHeers...

Hey buddy...On this specfic issue , if u think its diff or time consuming to fix this macro (one for CSS) ..than why dont we use the same macro which u mentioned in appraoch 2 and worked (other than an error that told u)..and have a query that replaces CSS with CSS-TDN or CSS-TSN etc etc in site extract sheet (i.e. main sheet)...means whatever we in Column B for CSS only should be replaced by values from Column V.....So it means ...when i run CSS macro or macros..it shud 1st do this and than con with remaining approach2 macro . What do u say ??

Originally Posted by rylo

Hi

Check the hotmail address.

rylo

replied sent back...kindly see...& reply..

Hi

Have you run the last lot of code I put on the hotmail?


rylo

Originally Posted by rylo

Hi

Have you run the last lot of code I put on the hotmail?


rylo

yes yes i did that...and it runs fine on sample sheet that i sent u earlier as well..but when run this code in my TEST sheet that has complete data..i get compile error...see i emailed u my test sheet...

and back again...

replied back rylo..check emaill...cheerz!!

Originally Posted by davesexcel

And the solution was????

Still waiting for final solution...code that we have has a small fixxing to be done..

Originally Posted by rylo

and back again...

thanks rylo , its works fine now..

here is the code , if some1 wud like to refer this...

Please Login or Register  to view this content.

Originally Posted by rylo

and back again...

Hi matey,

What are the chances of this code being re-used ? i mean currently we are doing it for RG2 , say if i need to do it for RG3 or RG4 etc what all shud be changed and all other cretaria remains same .?

i tried replacing all RG2 with RG3 but got stuck on following line while running the new code....

Sheets(1).Range("DataRng").AdvancedFilter Action:=xlFilterCopy, criteriarange:=OutSH.Range("A1:D2"), copytorange:=OutSH.Range("A4:E4")

Please Login or Register  to view this content.

Hi

If you change the RG2 in the arrays that form the headings for the criteria ranges (rows 49 and 63 in the code) you should bring back the relevant data.

You will also have to update the arrays for the replacement items to get rid of the RG? entries.


rylo

Originally Posted by rylo

Hi

If you change the RG2 in the arrays that form the headings for the criteria ranges (rows 49 and 63 in the code) you should bring back the relevant data.

You will also have to update the arrays for the replacement items to get rid of the RG? entries.


rylo

1. If you change the RG2 in the arrays that form the headings for the criteria ranges (rows 49 and 63 in the code) you should bring back the relevant data.- Did this .

2.You will also have to update the arrays for the replacement items to get rid of the RG? entries. - Not clear what you mean here ,any example ?

Hi

Have a look at code row 130. There is an array of items that gets replaced by "". The RG2 item is 5 from the end.

rylo

Originally Posted by rylo

Hi

Have a look at code row 130. There is an array of items that gets replaced by "". The RG2 item is 5 from the end.

rylo

row 130 is this ->

Next i

is this what u meant..i dont think see..confirm line no again plzz....and what to change and how..

Originally Posted by namz

row 130 is this ->

Please Login or Register  to view this content.

is this what u meant..i dont think see..confirm line no again plzz....and what to change and how..

Do you mean these lines needs to be changed for RG3 ,

Please Login or Register  to view this content.

can you if possible provide me what shud come here if i need to use this code for RG3 , once i know what changes u made & how and i will do this for RG5,RG6 etc...thanks..

Hi

In the sub formatGT, row 3 has the line

Please Login or Register  to view this content.

Change the RG2 in that line to be whatever you have used in the extraction.

If you are going to do a lot of this sort of changing, then it would pay to make a variable in Main that contains this item, and replace it with that variable for each appearance. You would have to include it as a variable in each of the sub routines in the same way that grpby and prod have been used.

rylo

Originally Posted by rylo

Hi

In the sub formatGT, row 3 has the line

Please Login or Register  to view this content.

Change the RG2 in that line to be whatever you have used in the extraction.

If you are going to do a lot of this sort of changing, then it would pay to make a variable in Main that contains this item, and replace it with that variable for each appearance. You would have to include it as a variable in each of the sub routines in the same way that grpby and prod have been used.

rylo

Thanks rylo....good one....i changed RG2 to RG3 in row 49,63 and in row3 of sub formatGT and it pulled info for RG3 for me....great job mate...thanks a ton....

Please Login or Register  to view this content.

Hey rylo ,

Thats our final code , can we have a small addtion in this code i.e. currently if we do not have any INTERNAL sites , we do not get a line "INT TOTAL" same happens with EXTERNALS, If no sites there "EXT TOTAL" does appear.

Can we change it & get the code to display "INT TOTAL" and "EXT TOTAL" irrespective of whether sites are there or not and base colour shud be grey.

Check in attached GIF image , the way this does not have any site for EXT but we still get "EXT TOTAL" displayed , can we get code to do this for INT AND EXT ?

Thanks in adv again...

Originally Posted by rylo

and back again...

BUMP....HELP PLZZZ....