Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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
0
Morgan
Asked:
Morgan
  • 7
  • 4
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now