inputting data from multiple worksheets and calculating costs of the results

Hello,

My work has several differnt copy machines with each employ having their own copy code. each month the copiers provide a usage report - listing all copies done for each copy code.

So far I am able to extract the number of copies from each sheet, however one copier does provide color: worksheet COPY-DUPCTR2 column H, but right now this data is not being pulled (I do need it to pull).

I also need when this report is run to extract which organization and fund the copy code is assigned to (this way the right department is charged.) this information can be pulled from the worksheet Fund and Org.

Now once the Run Report macro is finished I than want to run the calculate cost macro. This macro is to go through each column "Cost" on the Copy-Report and apply the charge: 0.04 for Black and white and .18 for color. The color is only applied to Column H on the Copy report.

Once all of the columns for Cost are complete, the totals need to be calculated at the end of the Copy Report (Columns AG and AH) I need to know the totals for each copy code (number of copies and total charge amount).

I have attached the workbook. All macros and buttons are on the Copy-Report sheet.

Any help would be great.

Thanks,

neo
LVL 1
MorganAsked:
Who is Participating?
 
SANTABABYCommented:
Notes:
1. We just need to keep the -COLOR suffix for special processing. I removed the -BW suffix.

2. Please make sure that you always put two empty rows between the end of report data and your footer.

Attached is the updated workbook.
Version-3-Copy-report---johnsmit.xlsm
0
 
SANTABABYCommented:
Please attach your latest workbook that includes fund and org worksheets.

From my work on your previous post:
In order to import data into your report worksheet, the name of each of your source worksheet(i.e. printer name) needs to appear in row 3 of the report sheet. Please make sure
the printer names in row 3 are all correct, if not , please make changes as necessary.
0
 
MorganAuthor Commented:
sorry for not attaching...

As for appearing in row 3, it does - COPY-DUPCTR2-COLOR

Also the costs need to been seen as currency.

Thanks!
Copy-report---johnsmith.xlsm
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SANTABABYCommented:
I has to simplify the buttons and the report sheet format for consistency and ease of coding.

Now for each copier:
#Copies indicates the sum of BW & color copies.
(If you want to show Number of BW & Color copies separately, the worksheet template will require some changes)

Cost indicates total cost including costs for BW & Color copies.
(If you want to show Number of BW & Color copies separately, the worksheet template will require some changes)

Feel free to add copier in the report sheet as needed (following the existing template. Just make sure that you do not change  the columns with labels:
Total Copies per copy Code      Total Dollar Amount for Copies Per Copy Code

As the labels are used to idenfy the columns where code specific totals are posted.      
      


All copy and calc logic is consolidated undet the run report code (button).

Hope this helps.
NEW-Copy-report---johnsmith.xlsm
0
 
MorganAuthor Commented:
Hello SantaBaby,

Great Job. Thank you so much.

I do need to include the color copies separate from the black and white, but this is only for column COP-DUPCTR2-COLOR (which I have added to the new workbook attached.) This inert of course moved the "Total Copiers per copy code" and "Total Dollar amount for Copies per Copy code" over to a new column.

Also for some reason the Run Report does not include the maintenance copy code 70, but on the worksheet COPY_DUPCTR1 at row 500 its listed for 1 copy. It should therefore be added to the Copy report.

Because I do answer to someone higher than myself, i'm sorry, but they now want to add a "footer" to the end of the Copy Report, which I have added to the attached workbook.) there is nothing you need to do in regards of calculating anything for this footer; I will be adding my own little excel codes to it, therefore I cannot have it affected with the clear button, other than moving it up but stopping two rows below, as seen in the attachment, from the "header". I would assume it would be fairly strait forward to just insert a new row when adding the copy codes to the report, pushing down this footer as each new entry is added. Is that a correct assumption? I'ved tried to talk them into not adding it but I'm not high enough on the pay scale. Again sorry for the new change.

Other than the concerns/changes stated above, its nearly perfect.

I appreciate your help with this.

Thanks,

neo
Copy-report---johnsmith.xlsm
0
 
MorganAuthor Commented:
SantaBaby:

Thank you so much! Wish I could actually pay you or give more than 500 points.

Thanks!

neo
0
 
MorganAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for neonate's comment #a38400579

for the following reason:

Outstanding!
0
 
SANTABABYCommented:
Neo,
Thanks for your appreciation. I'm glad to hear that the solution helps.

Please see the above posting from EE. It indicates that you intend to accept your own comment (ID: 38400579) as the solution.

Regards,
SB
0
 
MorganAuthor Commented:
Outstanding!
0
 
MorganAuthor Commented:
Santababy:

Wondering if you will be willing to give me a little more help with this project. I've openned a new question: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27879378.html

The overwhelming requests is to have the org in the copy report sheet be sorted from least to greatest.

Thanks,
neo
0
 
MorganAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.