Morgan
asked on
Extracting data from my Copy Report to another sheet
Hello All,
I have this neat VB code that extracts data from several different sheets creating my Copy Report of charges for each department. This report is a detailed report that lists all copy charges to a department and who made the charges. Another expert on here helped me with this. This report works great. So good in fact that the budget department would like to simplify their own reporting, from this report.
With that said...
What I need now is to make a less detailed report for the budget department. What this report needs to do is only extract the Fund, ORG and add grand total of the Org from the Copy-Report Sheet. The account will always be 5211 in this report (see attachment). This report is the JV Worksheet, within the Copy-Report---Johnsmith workbook attached. The JV worksheet will extract all charges from the Copy-Report, but instead of listing multiples of the same Org, it will only list one occurrence of the ORG but the Total will be all charges under that org.
for Example:
There are 15 occurrences of ORG 11101, when you run the Copy Report. But when I run the JV report it should list only one occurrence of ORG 11101 its assigned FUND number (most cases always 0) , List Account as 5211, and the total will be the grand total of all charges applied to the 15 occurrences 11101.
On the copy-report sheet, in column AH lists the total amount of each charge applied to a specific org.
On the JV worksheet, both the Run Report button and the Clear button should work similar to the ones found on the Copy-Report worksheet. The info that is already on the JV worksheet needs to stay when clearing and running the report.
Can anyone give me a hand?
Thanks,
neo
Copy-report---johnsmith.xlsm
I have this neat VB code that extracts data from several different sheets creating my Copy Report of charges for each department. This report is a detailed report that lists all copy charges to a department and who made the charges. Another expert on here helped me with this. This report works great. So good in fact that the budget department would like to simplify their own reporting, from this report.
With that said...
What I need now is to make a less detailed report for the budget department. What this report needs to do is only extract the Fund, ORG and add grand total of the Org from the Copy-Report Sheet. The account will always be 5211 in this report (see attachment). This report is the JV Worksheet, within the Copy-Report---Johnsmith workbook attached. The JV worksheet will extract all charges from the Copy-Report, but instead of listing multiples of the same Org, it will only list one occurrence of the ORG but the Total will be all charges under that org.
for Example:
There are 15 occurrences of ORG 11101, when you run the Copy Report. But when I run the JV report it should list only one occurrence of ORG 11101 its assigned FUND number (most cases always 0) , List Account as 5211, and the total will be the grand total of all charges applied to the 15 occurrences 11101.
On the copy-report sheet, in column AH lists the total amount of each charge applied to a specific org.
On the JV worksheet, both the Run Report button and the Clear button should work similar to the ones found on the Copy-Report worksheet. The info that is already on the JV worksheet needs to stay when clearing and running the report.
Can anyone give me a hand?
Thanks,
neo
Copy-report---johnsmith.xlsm
ASKER
Hello Santababy:
Thank you for your assistance again :) I really do appreciate it.
The JV report however is not calculating correctly. For example ORG 11101 the total should be $141.28, but the JV report reports only $30.52.
ORG 11102 reports 46.52, but it should be 22.60
ORG 11301 reports 265.96, but it should be 146.56
and these errors are occur for every ORG in the JV report.
I am getting these totals is from the Copy-Report sheet, column AH - adding the total from this column for the particular org. For example ORG 11101 starts at row 6 ending at row 20 (SUM(AH6:AH20)) resulting in 141.28, this is the total that should be showing in column Total on the JV report.
I thought it may be easier to obtain the data from the Copy-Report sheet, after its ran, rather than reading from each individual sheet like we do for the copy report - is this an incorrect assumption?
Again thank you for your help! It is deeply appreciated.
Thanks,
neo
Thank you for your assistance again :) I really do appreciate it.
The JV report however is not calculating correctly. For example ORG 11101 the total should be $141.28, but the JV report reports only $30.52.
ORG 11102 reports 46.52, but it should be 22.60
ORG 11301 reports 265.96, but it should be 146.56
and these errors are occur for every ORG in the JV report.
I am getting these totals is from the Copy-Report sheet, column AH - adding the total from this column for the particular org. For example ORG 11101 starts at row 6 ending at row 20 (SUM(AH6:AH20)) resulting in 141.28, this is the total that should be showing in column Total on the JV report.
I thought it may be easier to obtain the data from the Copy-Report sheet, after its ran, rather than reading from each individual sheet like we do for the copy report - is this an incorrect assumption?
Again thank you for your help! It is deeply appreciated.
Thanks,
neo
Ooops, at the end of calculation the records were sorted but only A,B,C columns, which messed up the Org<--> Total relationship. I've extended the sort to include D column.
Copy-report---johnsmith.xlsm
Copy-report---johnsmith.xlsm
ASKER
Santababy:
Thanks! Looks good. I am going to talk to the budget folks to see why there are no ORG's assigned to some of the copy codes, so that we can properly handle them in the JV report. I'll speak with them tomorrow in the am than we can rap this up :)
Thanks,
neo
Thanks! Looks good. I am going to talk to the budget folks to see why there are no ORG's assigned to some of the copy codes, so that we can properly handle them in the JV report. I'll speak with them tomorrow in the am than we can rap this up :)
Thanks,
neo
ASKER
Hello Santababy:
I've spoken with the budget folks and they tell me that for copy code 70 if we can just put Maintenance as the ORG that would be great and for Copy code 10357 label it as Course Packs as the ORG would be perfect. The others are still being updating and will have orgs assigned to them soon and therefore not relevant. However, it made me think that if we could still list the charges for copy codes that don't have an assigned org into the JV report that would be good as well, just putting in the ORG the Copy code and stating no ORG assigned.
If you have questions please don't hesitate.
Thanks,
neo
I've spoken with the budget folks and they tell me that for copy code 70 if we can just put Maintenance as the ORG that would be great and for Copy code 10357 label it as Course Packs as the ORG would be perfect. The others are still being updating and will have orgs assigned to them soon and therefore not relevant. However, it made me think that if we could still list the charges for copy codes that don't have an assigned org into the JV report that would be good as well, just putting in the ORG the Copy code and stating no ORG assigned.
If you have questions please don't hesitate.
Thanks,
neo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
Copy-report---johnsmith.xlsm