Insert the month wise datas of a model from various tables

[PRADEEPB270]

I have a sheet with 12 month wise datas of multiple models.Models may be 150 to 200 or so on.But months will be 12 in a sheet.

Now,I want to VBA codes working who helps me to create an automatic summary report of all the model with each month wise datas.

Please refer to my attach file for more clarification.

I want the '-summary-VBA' sheet .In this sheet 'Model' code is cell no.B4,B14 and B24 and so on.These model codes are appearing in the 'Month wise Details' sheet.Now,I want the datas of each model with each month wise, exist on the 'Month wise Details'sheet.

For an example,here,I have taken 3 model wise details,FNDADRKCC,GLRADRSCC and PPRADRKSC and the datas are column C,D and E.

Is it possible through VBA codes working?

[Rudi]

Hi Pradeep,

See if this will work for you.

One thing you need to do before you run the macro is to mark the Summary sheet (column A) with an "X" to identify the Model position on the Summary sheet. In the attached example you will see that I used an "X" in column A. The macro will scan column A for the "X" which identifies the position of each model that it will process. Once you have marked the summary sheet, you can run the macro that will collect the and populate the values in the summary. If there is no "X", no values are added for that model.

You will need to add more blocks for more Models, or keep the summary as a template and the macro will automatically fill it out based on the models in the B column.

Mat.Cost Working.xlsm

[PRADEEPB270]

Can it be possible to automatic insert the 'Model Name' as and when appear in 'Model wise Details'sheet ? This is so because I have at present 1190 model name and I think it will be very cumbersome exercise to put up each model name.Request you to please modify automatic insert 'Model Name' and avoid insert template exercise.

[HansV]

Here is a slightly different macro:

Code: Select all

Sub CreateSummary()
    Dim wshM As Worksheet
    Dim wshS As Worksheet
    Const lngFirstMRow = 4
    Const lngFirstMCol = 2 ' B
    Const lngFirstSRow = 4
    Const lngFirstSCol = 2 ' B
    Const lngCount = 10
    Dim lngLastMCol As Long
    Dim lngMCol As Long
    Dim lngLastMRow As Long
    Dim lngMRow As Long
    Dim lngSRow As Long
    Dim lngSCol As Long
    Application.ScreenUpdating = False
    Set wshM = Worksheets("Month Wise Details")
    lngLastMRow = wshM.Cells.Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    lngLastMCol = wshM.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Column
    On Error Resume Next
    Set wshS = Worksheets("Summary")
    On Error GoTo 0
    If wshS Is Nothing Then
        Set wshS = Worksheets.Add(Before:=wshM)
        wshS.Name = "Summary"
    Else
        wshS.Cells.Clear
    End If
    lngSRow = lngFirstSRow
    For lngMCol = lngFirstMCol + 1 To lngLastMCol
        wshM.Cells(lngFirstMRow, lngMCol).Copy _
            Destination:=wshS.Cells(lngSRow, lngFirstSCol)
        wshM.Cells(lngFirstMRow, lngFirstMCol).Resize(RowSize:=lngCount - 2).Copy _
            Destination:=wshS.Cells(lngSRow + 1, lngFirstSCol)
            lngSCol = lngFirstSCol
            For lngMRow = lngFirstMRow To lngLastMRow Step lngCount
                lngSCol = lngSCol + 1
                wshM.Cells(lngMRow - 1, lngFirstMCol).Copy _
                    Destination:=wshS.Cells(lngSRow + 1, lngSCol)
                wshM.Cells(lngMRow + 1, lngMCol).Resize(RowSize:=lngCount - 3).Copy _
                    Destination:=wshS.Cells(lngSRow + 2, lngSCol)
            Next lngMRow
        lngSRow = lngSRow + lngCount
    Next lngMCol
    wshS.UsedRange.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

[PRADEEPB270]

Hi Hans,

Please refer my attach file .There are some problems.Only those models are appearing which are common with 'Apr-13'.
Take an example,refer cell no.Z24 in the sheet 'Month wise details',this model is not appearing and like this ,there are so many model code which are not appearing.Also refer the sheet 'Summary',from cell no B234 and till last,these are no requirement.

I have attach a sheet that is expected result.Request you to please transpose each model into this format.
Hans Sir,I have provide the required format in my first post,I think it will be better for more clearity.

[HansV]

The macro will only work if each column contains a single model. Otherwise, it is too complicated to manage.

[PRADEEPB270]

If it is possible,requested you to please help.Such a big task for me and reduce a lot of time from my working schedule.

[HansV]

Can you restructure the "Month Wise Details" sheet so that each model has a column of its own? The macro should work then.

[PRADEEPB270]

Thanks for the suggestions.

[Rudi]

Try this new version...

Mat.Cost Working.xlsm

[PRADEEPB270]

Yes,Rudi Sir,this is the finest working as required.VBA codes are working well as requirement.Very glad to find it.Once again,many-2 thanks to you sir.

Have a nice day.