PICK OUT/EXTRACT column header "COMMON" IN ALL 5 SHEETS

[panyagak]

Hi Excel gurus

Please help.

My 4 worksheets are named: Wk1, Wk2, Wk3 &
Wk4, Wk5. In the 5 sheets, columns A (Date) and B ( Subject name) are similar. I need a vba code to
pick out only"Subject names" that ARE COMMON in ALL THE 5 sheets and paste them in a new sheet, "New All".

Am tired of copy, paste & sort more than 20,000 rows!!

HELP!!

 

[panyagak]

hi Gallen.

Just copied & pasted the code under 'Insert module'. Am trying to RUN the code and its only picking the column headers. The code seems not to loop through the subject names as expected.

regards
Patrick

 

[gallen]

I'm home now. Will look again tomorrow but this was tested and worked for me.

 

[panyagak]

ok. Will contact you tomorrow.

 

[gallen]

I have tested again and works as I expect so it doesn't work as YOU expect. You will need to provide an example of how your data is displayed, and how you expect the result.

The code I provided loops through all subject names in Wk1. It then check Wk2 - Wk5 to see if that name exists in that sheet anywhere. If it is found in all sheets it copies the data from Wk1 to 'New All'

 

[panyagak]

Thanks Gallen.

Is it the first or second code you sent me that you're using?

Column A header is "Date" in cell A1 : Column B is "Subject Names" in cell B1 for all sheets. Then data begins in Cell A2 horizontally & vertically.
Then Sheet "New ALL" is empty & the last.

I ONLY need subject names 1st, the rest of row data if the code extracts it, the better

Also advise on Font e.g "Subject Names" & "Subject names" versus which of the 2 is picked by the code or there is no difference?

May be you can advise on how your sheet displays - its BETTER - Just a sneak preview of your 1st 3 rows: am not near my PC now.

Should I select the range per sheet or its unnecessary?

Thanks

 

[panyagak]

Another point is Subject names have been sorted A to Z in the Wk1 to Wk5 sheet. Its here you notice 2-3 repetition of Subject names in any of the 5 sheets

 

[panyagak]

Gallen.

Congratulations, it worked!!

Had to interchange sheets to suit the code - the problem was on my side!!

Thanks & God bless you.

Patrick

 

[gallen]

Alls well that ends well. Happy it works.

 

[panyagak]

"One good turn deserves another" - A pat on your back!!!

God Bless.

 

[panyagak]

Hi Gallen.

Thanks for your solution though sadly am not getting exactly what I want. Am wondering if you can alter the code to suit the expected outcome.

Initially, I had worksheets named Wk1, Wk2, Wk3 & Wk4. You forced me to introduce Wk5 ( data in sheet 5 (Wk1) is unrelated to Wk2-Wk5!!

I need to retain these 4 sheets only: I NEED you to re-adjust the code to rumange/loop through only the 4 sheets, pick out only the subject names appearing in the 4 & post the result in "New ALL" sheet.

The 5th worksheet or any other sheet is unrelated to Wk2 to Wk5:thats why am not getting the expected result: you may ask;how do I know am not getting what I want??

I manually merge, sort then, Sub totals of Wk1-Wk5 & compare with "New ALL" from the code & the results are different. Lets work around only the 4 sheets.

Hope my point is clear.

Regards
Patrick

Happy Easter Weekend