Previous Topic Next Topic

Auto generate the seller sales report

Solved/Closed
Winndixie Posts 23 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 - Sep 9, 2014 at 11:13 AM
Winndixie Posts 23 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 - Sep 26, 2014 at 09:31 AM
Hi!

I create a sales report and I set up total 6 worksheet

Worksheet 1 I named it Price list ... is a Data for my sales report. and I set a code, name, points and amount

worksheet 2-5 I named by sellers name which is individual sales report by the seller, then it mind be including other seller name.

the last worksheet i named it summary for my reporting. in this report i differentiation by seller name. i need to report individual total actual sales by the seller

i wonder how do I set when my seller worksheet that which if name Pauline, in her sales reporting got including another seller name Carmen, then my summary will auto show the sales under Carmen sales.

Here I enclosed the sample excel at below link FYR.

[code]http://speedy.sh/Zr5bX/Sample.xlsx/code

Thanks and reagards
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 9, 2014 at 12:04 PM
Hi Winn,

Your colour scheme looks really nice.

On your "Carmen" sheet you have 2 entry's, one where the buyer is Pauline and the other one being Carmen.

If I understand you correctly you want to move each line to the appropriate name of the "Summary" sheet.

A few questions arise:
How do you foresee this working?
- When you enter the name, the row will automatically be moved.
- When you run the code manually.
- Or......

Do you want to process all named sheets at once or one at the time?

Are there any limitations in the number of rows used?
- 5 rows per name on the "Summary" sheet.
- 10 rows on the named sheets.

I cannot find the last two columns on the "Summary" sheet, "Total RM" and "Account", on the named sheets.

Let me know your thoughts on this.

Best regards,
Trowa
0
Winndixie Posts 23 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018
Sep 15, 2014 at 05:54 AM
Hi Trowa,

Thanks :)

there are no limitation in the number rows used, is depend on the products they buy which count by the points

e.g: if the product only 1 point per bottle, we had set a target 35 points, so maybe they need to buy more than above 10 products to meet the target. for this report i try to create 4 account which share by 4 ppls so that they can share the account to buy product by sharing points to meet the target 35 points then i need the excel work which i name it summary help me auto generated and classification the product sorting by the buyer, then I can know the product which use Carmen account buy but actual is belong to Pauline or others and the total amount they spend on the month.

"Total RM" was same as the "Total" at [name] worksheet and "Account" same as buyer.


thanks and regards
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 15, 2014 at 11:54 AM
Hi Winn,

I think I got something nice for you.

- To clear the Summary sheet, I used a hidden Template sheet, which replaces the "messed up" Summary sheet with possible added rows.
- The code will loop through all the specified named sheets.
- Copies all data rows and pastes them in the appropriate tables in the Summary sheet.
- Rows are automatically added to the Summary sheet, so that there is always an empty row in each table.

Here is the code: 
Sub RunMe()
Dim ws As Worksheet
Dim lRow, eRow, x As Long

Application.DisplayAlerts = False
Sheets("Summary").Delete
Application.DisplayAlerts = True
Sheets("Template").Visible = True
Sheets("Template").Copy Before:=Sheets(1)
Sheets("Template (2)").Name = "Summary"
Sheets("Template").Visible = False

For Each ws In Worksheets
    If ws.Name <> "Kelly" And ws.Name <> "Christina" And ws.Name <> "Carmen" And ws.Name <> "Pauline" Then GoTo SkipSheet
    
    ws.Select
    x = 2
    
    Do
        eRow = Sheets("Summary").Range("A:A").Find(what:=Cells(x, "D")).Row
        lRow = Sheets("Summary").Range("A:A").Find(what:=Cells(x, "D")).End(xlUp).Offset(1, 0).Row
        
        Sheets("Summary").Range("A" & lRow) = Cells(x, "B")
        Sheets("Summary").Range("B" & lRow) = Cells(x, "C")
        Sheets("Summary").Range("C" & lRow) = Cells(x, "E")
        Sheets("Summary").Range("D" & lRow) = Cells(x, "I")
        Sheets("Summary").Range("E" & lRow) = Cells(x, "D")
        
        If eRow - lRow = 1 Then Sheets("Summary").Rows(eRow).Insert
        
        x = x + 1
        
    Loop Until IsEmpty(Cells(x, "B"))
    
SkipSheet:
Next ws

Sheets("Summary").Select

End Sub

Best regards,
Trowa
0
Winndixie Posts 23 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018
Sep 17, 2014 at 08:55 AM
Hi Trowa,

~.~ sorry I didn't get the point.

I had try to insert the code...then it automatic deleted the summary worksheet, did I misunderstanding the code??

I try run the code then came out:-
Run-time error '9':
Subscript out of range

Can you do a sample file for my reference?

Sorry... >"<
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 18, 2014 at 11:16 AM
Hi Winn,

No worries, I probably should have been more clear in that you needed to create a duplicate of the Summary sheet. Then rename the sheet "Template" and hide it.

Here is your file with the code implemented and with the hidden "Template" sheet:
http://speedy.sh/HYJhA/Winndixie-Sample-with-code.xlsm

So the "Template" sheet is only used to 'clear' the "Summary" sheet. But instead of clearing data, restoring row counts and colours, I decided to simply delete the "Summary" sheet. Then unhide the "Template" sheet, which looks the same as an empty "Summary" sheet, make a copy of the "Template" sheet, rename it "Summary" and then hide the "Template" sheet again.

The run time error occurred because the "Template" sheet could not be found.

Feel free to ask any questions you might have?

Best regards,
Trowa
0
Winndixie Posts 23 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018
Sep 23, 2014 at 08:35 AM
Hi Trowa :)

Thanks a lot...

My Excel is 2007 version...after I download the file, it came out erro again. It is possible that different version cause me cannot open the file??

Thanks and regards
Winn
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 23, 2014 at 10:57 AM
Hi Winn,

I wasn't aware there were compatibility issues between 2007 and 2013.

Here is the file saved as 97-2003 version:
http://speedy.sh/XzMCa/Winndixie-Sample-with-code.xls

If that doesn't work, then try your own sample file again. Make a copy of the (empty) "Summary" sheet > rename the copied sheet "Template" > hide the sheet. Then run the code.

Hopefully that works.

Best regards,
Trowa
0
Winndixie Posts 23 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018
Sep 26, 2014 at 09:31 AM
Hi Trowa,

Still not working on the file....

anyway thank you for helping me solve the problem :)

best regards
Winn
0

Similar discussions

Facebook Login Issue
meo792002 -
Mariel -

3 responses
Report login issues Facebook
JamalAhmed -
Ambucias -

61 responses
Any auto clicker for Xbox One?
vic11con -
julietdcosta -

13 responses
reporting a login issues facebook
elzerbanayim -
Inweregbuprisca -

2 responses