Help With Excel Totals Manipulation

[bstafford]

Hi,

Need some help in Visual Basic on how to manipulate an excel report. This report has sales day-to-day, part-to-part input from a database system and each part shows monthly sum totals. I have created the original report from crystal and then export to excel, now need to configure the report with each part on a row and each column by month and only show to report the monthly qty and monthly sales amount for that part/customer. The report is sortable and the part numbers are created in a way that each customer is different by the first 4 numbers of the part number and would like to export this to a new worksheet in the same excel file workbook.

The original report is to busy with many part numbers that get converted during the month and sales is only interested with the Total cumulative sales amount for each part for each month.

How can I take the sum total cell of each part, and show in a new worksheet for the last 6-12 months.

I would like to show what I have and what I am trying to achieve with the new report. The report that Sales are currently using is done manually by inputting the amounts of each part for each day for each customer and the result is shown in excel.

I have a database program that I export into crystal, then excel, but need to get the data into a different format, I can show this format in excel that I currently have.

It is difficult to explain, so can a file be uploaded to show what I am trying to accomplish?

Can anyone help me with this please?

Regards

Bill

 

[bstafford]

Hi,
I have started on the code for the worksheet by formatting the cell, but still looking how to do this movement of cells to a new worksheet and show only the monthly total of each part for each month. Can someone help me get started on what to do here please? I am new with VB and struggling what to do.

Sub Macro5()
'
' Macro5 Macro
'
    Columns("C:C").Select
    ActiveWorkbook.Worksheets("Export_MO_Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Export_MO_Data").Sort.SortFields.Add Key:=Range( _
        "C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Export_MO_Data").Sort
        .SetRange Range("A2:CE289")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Can files be uploaded to show what I am doing in this project?