Challenging issue - references

Dears ,

I need your help please if in this issue, I am currently working in production planning , I have the worksheet example attached, the sheet shows you a weekly calendar, i am finding an issue in having the information to be referenced in a tabular view where i can use this information as a pivot table, if you open the work sheet, you will find week numbers of every month , squared slots, manual input of information such as customer name, product name, batch size,etc.(information that i use to plan production. as mentioned before and shown in my attachment, i have mutiple lines and i need the sheet name to be also reflected so i can have relevant informationo

please help me out in this .

Try:

Please Login or Register  to view this content.

Hi Faris,

Thanks for the rep!


Big Difference - Try:

Please Login or Register  to view this content.

*This routine will wipe out your formula in Column A - if you want to keep that formula there, we can comment out that bit of code

Oh this is great, now one last thing, is there anything we can do to have a full look over 6 months or i dont mind if we take the months out just make the calendar for a full year view. !!

amazing job !! thanks a lot


do you think also it would be easy to copy this sheet into for five another ones in the same workbook. ? since i need to retrieve this information for 5 different production lines

It should work as well with a full calendar or more

It should work with a full calendar - would you like to be able to limit which months it can process?

Originally Posted by xladept

It should work with a full calendar - would you like to be able to limit which months it can process?

Hello Xladept,

I have added a final file for you that shows all what i need , its name is Mastersheet2, what i need is shown in the file, you will notice that i have full overlook over the months of the year in the calendar, now my problem is that i could not add another worksheets to this excel file (line2,line3,line4,line5), because the fille will become too large, so now the issue is how to make a macro to retrieve the information from those multiple worksheets into one worksheet which table analysis.

I have been trying to do it for 3 days ! its not working with me ! appreciating your help

Please also mind that i need the worksheet name to be also added to the table, I wonder if I SHOULD ADD IT to the calendar if it will make it any easier.

Double Post Disservice

Hi Faris,

Thanks for the compliment - this works with your new sample, but you must be patient as it takes a couple of minutes to run:

Please Login or Register  to view this content.

Thanks a Million, It works great !!! how can we edit this code also to work on all worksheets (i.e. if i want to add line2,line3,line4,line5) ALONG WITH THE DATES THE date is actually the most important it has to be in the first column

TRU GURU ! .

Thanks again! if you can comment on the code it would be great as well

I'll get back to you tomorrow

Hi Faris,

This works with your sample, although the output fields now include the record's date in column 1

Please Login or Register  to view this content.

Hi Faris,

This works with your sample, although the output fields now include the record's date in column 1

Please Login or Register  to view this content.