Report macro

Hi,

I need to write a macro for a report where I have about 500 material numbers and each have about 10 batch numbers. I need to set the macro to tie up the forecast usage against stock on hand. When I do it manually it takes me days to do can any1 please assist me?

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Hi Roy.

Thanks for that but currently I am doing it alone. Will I need to run a dummy workbook for each material?

What I am trying to get is the SLOB stock ( slow moving obselette stock ) for each item. This means that when I run a report from SAP then it gives all materials and all batches. I need to tie the Forecast for each material up at a batch level. The only way that I can do that is if I do Material for material. How would or could I write a macro for that? I would have attached a example but the material is very sensitive and I cant share it with everyone on the forum. Is there a way I could send you the info other than on the forum?

Hi nightcrawler-jay;

Your description of your problem doesn't help anyone figure out how to solve it.

Delete all sensitive data from a copy of the file (making it a dummy file). Put some phony data in it's place so we can see how it's formatted, and how you want the finished result to look. Then we can help.

Good morning

The reports ran is for all items and I want to write a macro to split each item into a separate sheet. I have 935 line items which I need to split. Reason for me needing to split the items is that some items have little or no forecast and other have more forecast than total stock. The idea behind it is to get the forecast to consume the oldest stock first and after the forecast has been removed from stock on hand I can then determine the SLOB ( slow moving obsolete stock ) that I will be left with.

Hi nightcrawler-jay;

You didn't include a sheet that showed an example of how you wanted the results to look.

Here's how I would handle it based on what you have said so far: I would write a macro that created a sheet named "9080007" and copy all the data for 9080007 from Sheet1 onto that sheet.

If that's all you want, an easier way is just use "autofilter".
In the menus: DATA>FILTER>AUTOFILTER. That will make every column heading like a listbox. You just go to "Material Number" heading and select the material number you want, and it is automatically filtered. Then you can print the sheet as is. When you're done printing, you can turn off autofilter the same way DATA>FILTER>AUTOFILTER

If you want the macro to figure out what batches to include in a report, you will have to tell us how to figure it out. Describe it like you would describe it to a new employee that doesn't know anything at all about your business. Remember I'm a new employee, and I have no idea what SLOB or SAP or Material Group are, so explain it to me that way, and I can write a macro to do it for you.