Auto generate the seller sales report
Solved/Closed
Winndixie
Posts
20
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
-
Sep 9, 2014 at 11:13 AM
Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 - Sep 26, 2014 at 09:31 AM
Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 - Sep 26, 2014 at 09:31 AM
Related:
- Auto generate the seller sales report
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Stop facebook auto refresh - Guide
- Grand theft auto iv download apk for pc - Download - Action and adventure
- Crystal report viewer download - Download - Data management
- Auto redial samsung - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Sep 9, 2014 at 12:04 PM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Sep 15, 2014 at 11:54 AM
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:
Best regards,
Trowa
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
Winndixie
Posts
20
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
Sep 17, 2014 at 08:55 AM
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... >"<
~.~ 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... >"<
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Sep 18, 2014 at 11:16 AM
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
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
Winndixie
Posts
20
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
Sep 23, 2014 at 08:35 AM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Sep 23, 2014 at 10:57 AM
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
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
Winndixie
Posts
20
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
Sep 26, 2014 at 09:31 AM
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
Still not working on the file....
anyway thank you for helping me solve the problem :)
best regards
Winn
Sep 15, 2014 at 05:54 AM
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