VBA Query: Copying specific sheets with parameters to a mastersheet

[kk22]

Hi All,

In need of some VBA widsom! i'm very new to VBA and am completely stuck.

I have attached a workbook of the exact template that i'm working on.

I need a code to help me copy data from Row 9 and below from each of the worksheets - Advertising,CRM,Promo & Animations, POS into 'Forecast Mapping' Sheet. Please note when coding i need it to name the specific sheet name of each and not 'all sheets in work book' as i have other sheets that will clash.

The order of pasting should be as above.

I only want the macro to paste whole rows if there is a value anywhere between J10:U10 and below.

This is to ensure that the data i copy to the 'Forecast Mapping' have costings attributed to it.

Once Advertising (1st) has been copied to the 'Forecast Mapping', it then needs to copy CRM (2nd) and paste rows directly underneath the last row used. This should also apply for Promo & Animations (3rd) and POS (4th).

Any help would be much appreciated!! i have look at so many codes for this but can't get the right coding to work. keeps returning errors.

Thank you all!

KK

[mikeTRON]

So you want to copy and paste ONLY those 4 sheets? You wont ever want to do more?

How many other sheets can exist? What logic can be used to determine IF the sheet has data you want besides the name?

[kk22]

Hi mikeTRON,

thanks for getting back to me, its very much appreciated.

yes, i would only like to paste to those 4 specific sheets. there will be other exisiting sheets in the same workbook perhaps 8 more but they are not interrelated so i need a macro that targets specifically those sheets.

The logic that i would use is if there is a value say $100 entered in any of the 4 sheets 'Advertising, CRM, Promo & Animations and POS between columns J and U from row 10 and downwards, then the macro would copy that row and paste it into the 'Forecast Mapping' sheet. Columns J and U are numbered 1 - 12 to represent months. so if there is a cost is any of the months, then it should be copied into the forecast mapping sheet.

I hope this clears it up but if it doesn't, please let me know and i'll try harder to explain.

Once again, your help is so much appreciated.

thank you!
KK

[tigertiger]

Plz, put the following code to a module, and try to run it to solve your problem,

Please Login or Register  to view this content.

you can easily change the name of the sheets or the order that the sub refer to them, by change the code-lines in above sub

Please Login or Register  to view this content.

I hope, you 'll enjoy the solution