Auto generate the seller sales report [Solved/Closed]

Report
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
-
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
-
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

2 replies

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
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
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018

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
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
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
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018

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... >"<
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
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
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018

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
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
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
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018

Hi Trowa,

Still not working on the file....

anyway thank you for helping me solve the problem :)

best regards
Winn