How to create New Worksheet by part

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

Since I am new to VB and trying very hard to do this project, I am hoping for assistance here to get me thru the coding to understand how to complete this.

I have started on my worksheet and have got to a point of being able to sort it by part number. It is currently working okay to this point, but don't know where to go from here.

Can someone tell me if this is good code?

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

How do I start to grab data from a cell and put it into a new worksheet in this same file?