Automatically report prepared in excel by VBA

Muhammad Safiuddin Khan
Muhammad Safiuddin Khan used Ask the Experts™
on
Dear All,
Is there any excel expert can solve my problem, I am stuck to write macro code for this case.
Need report by Excel VBA

Date      Order No.      Party Name      Sale      Return      Total sale
1-Jan-17      60827      Wear       37.00        -          37.00
1-Jan-17      51530      Mills       29.00        -          29.00
2-Jan-17      60531      Polar       1,357.90        -          1,357.90
2-Jan-17      60480      Adury       357.00        -          357.00
2-Jan-17      60827      Polar       24.00        -          24.00
3-Jan-17      51530      Wear       490.00        -          490.00
4-Jan-17      60531      Polar             94.90        (94.90)
5-Jan-17      60480      Wear       223.00        -          223.00
6-Jan-17      60827      Polar       741.60        -          741.60
7-Jan-17      51530      Polar       43.80        -          43.80
8-Jan-17      60531      Polar       18.90        -          18.90
9-Jan-17      60480      Wear       355.00        -          355.00
10-Jan-17      60827      ARS       287.00        -          287.00
11-Jan-17      51530      Ocean       98.00        -          98.00
12-Jan-17      60531      Taqwa       818.00        -          818.00
13-Jan-17      60480      Knit       634.00        -          634.00
14-Jan-17      60827      Wear       263.00        -          263.00
15-Jan-17      51530      Mills       268.00        -          268.00
16-Jan-17      60531      Taqwa       448.50        -          448.50
17-Jan-17      60480      Wear       418.00        -          418.00
18-Jan-17      60827      Wear       559.00        -          559.00
19-Jan-17      51530      Wear       130.00        -          130.00
20-Jan-17      60531      Taqwa             170.00        (170.00)
21-Jan-17      60480      Taqwa       118.00        -          118.00
                              
      GQC sheet                  

I have a huge raw data in  "GQC sheet" and need help to make report like below "Report sheet". to find total sale depend on Order no, and party name, when I write a Order No. in my 'Report sheet' in cell D2, report will prepare automatically in "Report sheet", like my example "Report". plz see attach file color indication in GQC & report sheet .

Order No.      51530                  
                        
Order No.Party Name      Sale  Return         Total sale
51530          Mills                297        -          297
51530         Wear                620        -          620
51530         Polar                 44        -          44
51530         Ocean                  98        -          98

Note: I do not want to use pivot table for this case, coz, there is huge number of data in GQC sheet and number of report huge that's why i wanna do it by VBA,, Really I need some one help. Thanks in advance.

WBR

Safi
Example--.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Is this on the right lines. If so I can tidy it up
EE-Pivot.xlsx
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,

Please try attached...I have created both Pivot Method plus Dropdown Automation. Whichever way you may prefer. Please let me know what changes you may need.
Example-Extract-Datas.xlsm
Muhammad Safiuddin KhanSenior Executive

Author

Commented:
Dear Shums,
thank u such.
it's working
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Safi! Glad I was able to help.
Roy CoxGroup Finance Manager

Commented:
Why use all that code to produce a PivotTable which I did manually? The only code really necessary would be to refresh the the PivotTable and by converting the data to a Table the data would be dynamic.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial