July 27th, 2011, 01:54 AM
Create New Worksheet & Transfer data
I have been trying to create a report but not having a lot of success. Here are the details:
I start with Crystal Reports X for reporting, export to EXCEL 2007 & start VB. My VB experience is limited. I have created a report with 3 Group Headers:
Part No. (Group)
The detail Example data export into Excel looks like this from left to right column display:
Date Due - Part No - Part Description - Customer No. - Qty Del - Total Sales
05/07/2011 - 0001-012345 - BB-5x1x349mm - 123456789 - 1,200 - £100.01
25/07/2011 - 0001-012345 - BB-5x1x349mm - 123456789 - 1,200 - £100.01
2,400 - £200.02
There is of course many more rows of data with the result for each part, I have them grouped by Part No, then by Month in the excel sheet. Then I have a Total Sum under the Total Sales Column £200.02
This is all fine as the way it looks and works, but my problem I am trying to solve is taking this data I have in this Excel sheet, This example of 1 part number and make another worksheet that just has each part number showing on each row, with the above column descriptions that are from the original worksheet and show the monthly total, monthly qty for each part in columns going across.
My sales team want this type of report that shows the same part number to display only once for that entire month, then only the total qty and total sales for the month on the new worksheet as I describes above..
The problem with the original report is to much scrolling for the same answer, and need to just show the results, not each sale of the same part number
After exporting the Crystal report into my new excel worksheet, how can I do this with VB to show each part number SUM/Cumulative values that will go across horizontally in column/month format in the excel spreadsheet, I want to pull only the part number, description, all details showing only once for each part and the Sum of that Sales and Qty sold?
Sorry if I repeated myself and hope the explanation is good.
I appreciate any help anyone can provide. I only have a few days to build this before they manually input the data the way they want it in Excel.
Please can anyone help and let me know what additional info you need.
Can I upload the Excel File that Sales want the data format to resemble, and also uploaded to the Excel export report I have coming from my original Crystal reports?
Click Here to Expand Forum to Full Width