merge data and summing values & create report

hello
I have this sample simple just to understand what I want , my original data begins from COL A to COL H it should create whole data with the headers from COL K: S , so each column contains duplicated strings as in COLS B,C,G should merge as one , as for the values should summing for duplicated data and insert column(TOTAL) to summing the values for any item I put the result from COL K:S and my real data at least 5000 rows and every time is increasing
thanks in advance

According to your attachment a VBA demonstration as a beginner starter to paste to the 'collection' worksheet module :

PHP Code: 

Sub Demo1()
     Dim R&, S$
         UsedRange.Columns("K:S").Offset(1).Clear
         Application.ScreenUpdating = False
    With [A1].CurrentRegion.Rows
        .Columns(2).AdvancedFilter 2, , [L1], True
         R = [L1].CurrentRegion.Rows.Count
         Range("M2:M" & R).Formula = "=VLOOKUP(L2,B$2:H$" & .Count & ",2,FALSE)"
         Range("Q2:Q" & R).Formula = Replace([M2].Formula, ",2", ",6")
         Range("N2:P" & R & ",R2:R" & R).Formula = Replace("=SUMIF($B$2:$B$#,$L2,D$2:D$#)", "#", .Count)
         Range("S2:S" & R).Formula = "=SUM(N2:R2)"
    End With
         Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

thanks
it gives error object required
[HTML][UsedRange.Columns("K:S").Offset(1).Clear/HTML]

As it works as expected on my side …

any solution ?

… so well read post #2 and paste the code where it must be rather than any standard module …

thanks
but I have two questions , why it doesn't work in standard module and this is my mistake and can you mod the code to make create the headers with borders and formatting as in range from COL A to COL H when merge data ?

According to your attachment headers are already created but as a beginner starter you can amend the code to add anything …

It can not work in a standard module as it is designed for a worksheet module but for a standard module
you can add a valid worksheet reference to all statements relative to the worksheet …

Originally Posted by ABDELFATTA

each column contains duplicated strings as in COLS B,C,G should merge as one

Please Login or Register  to view this content.