PDA

View Full Version : Solved: vba help to automate report.



VISHAL120
09-09-2011, 04:13 AM
Hi ,

I needed help to automate this report if possible. Right now this is done manually this take enormous time. Thanks to see attached file for better idea.

I have a main order Book where everyday or week we add new order to process and to ship.
And based on this we actually need to give our production dept and shipping dept list of orders to be exported by week and delivery.

What I doing actually are:

Make a sorting by delivery then by customer.
Copy the standard header format for shipping.
copy the order by delivery and by customer from the main order book and paste on the reports to make the shipping schedules.
make a total for each customer on the bottom ( please see the attached file)
and repeat for every customer.
the report shall be separated for each customer and by delivery and every time there need to place the standard header format and sometimes there are other standard format header that shall be used for specific customer. See example.
I would be grateful if I can get some hint and help on how I can do this with vba.

Thanks and regards,

Kenneth Hobs
09-09-2011, 05:16 AM
Seems doable. If you can wait a day or two, I am sure that we can help.

After the report is generated, did you want to mark the data as reported in some way or move it to another sheet or something else?

VISHAL120
09-09-2011, 05:54 AM
Hi Kenneth,

many thanks for the reply. I will wait as this takes alot of time to do and also lots of mistakes sometimes.

Yes will need to be marked reported as per date generate and it shall be transfer to a new workbook to keep the record seperately.


thanks again.

mohanvijay
09-10-2011, 09:02 AM
Hi Vishal,

Try attached file

For custom heading i change 'standard_header' sheet as follows
1.Header must begin from column 'B'
2.In Row 2 must be standard header
3.for custom header enter customer code in column 'A' and
paste custom header in column 'B' and next row of custom header
specify columns to goto report in attched file like this
'A' For Customer column in report sheet
'B' For Order column in report sheet
'C' For Knit column in report sheet
'H' For Quantity column in report
'E' For DelDate column in report

i am not adding code for format Report sheet

VISHAL120
09-11-2011, 10:05 PM
Hi Vijay,

Many thanks for your precious time. its working great and its what i needed. Now Am sure we will not make that much mistakes when giving our report to shipping dept.

by the way can you please help on for the formatting please.

I will start integrating the the code in our main order book meanwhile.

mohanvijay
09-12-2011, 01:32 PM
Try attached file

VISHAL120
09-12-2011, 09:54 PM
HI Mohan,

Many Thanks again its ok .

Can you please help me again as i have added some more data on it like the packing type etc. and i have been trying to add these columns reference on the code but its not taking the data to place on the report.


can you please just help me on how you are addressing and also if you can explain me what the
Set OO_Dic = CreateObject("Scripting.Dictionary") means as i have never come across this before.

Thanks again sir.

mohanvijay
09-13-2011, 12:33 AM
Try attached file

Scripting.Dictionary is object from Scripting library which you may reference in VBA 'Microsoft Scripting Runtime'.

Dictionary object holds key and data for that key

reference scripting library in VBA and see that objects and methods in object browser

VISHAL120
09-13-2011, 01:10 AM
Hi ,

thanks a lot for your help and support.