To calculate based on names

[GirishDhruva]

Hi Everyone,

Here i am trying to calculate the value based on the headers which are in the "sum" sheet and their names will only be A,B,C......, and my inputs are in "main" sheet but in my input those columns will be renamed as Sum of A,Sum of B,Sum of C..........., and my preferred output should be in "main"sheet only .

Here i have attached my sample workbook.
https://www.dropbox.com/s/fitbvqde265b9e0/mrexcel_sum.xlsm?dl=0

Regards,
Dhruva.

 

[GirishDhruva]

Sub calculate_based_on_names()
  Dim sh1 As Worksheet, sh2 As Worksheet, r1 As Range, r2 As Range
  Dim lr As Long, lc As Long, lc2 As Long
  Dim c As Range, f As Range, i As Long, col As Long, j As Long
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Main")
  Set sh2 = Sheets("sum")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.[COLOR=#0000ff]Range("A3")[/COLOR].End(xlToRight).Column
  Set r1 = sh1.[COLOR=#0000ff]Range("A3"[/COLOR], Cells(lr, lc))
  lc2 = sh1.Cells(3, lc + 2).End(xlToRight).Column
  Set r2 = sh1.Range(sh1.Cells(3, lc + 2), sh1.Cells(3, lc2))
  r2.Offset(1).Resize(lr).ClearContents
  For Each c In r2
    Set f = sh2.Rows(1).Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      col = f.Column
      For i = 2 To sh2.Cells(Rows.Count, col).End(xlUp).Row
        Set f = r1.Find("Sum of " & sh2.Cells(i, col), , xlValues, xlWhole)
        If Not f Is Nothing Then
          For j = c.Offset(1).Row To lr
            sh1.Cells(j, c.Column) = sh1.Cells(j, c.Column) + sh1.Cells(j, f.Column)
          Next
        End If
      Next
    End If
  Next
End Sub

TO run the above code first we should have the headers in the main sheet.

So instead of that, first can we copy the headers from "sum" sheet and paste in last empty column in main sheet, then the above code should run.

Regards,
Dhruva,

 

[DanteAmor]

Following the example in post #2 , do you want to copy cells A1 to D1 of the "sum" sheet and paste them into cell W3 on "Main" sheet?

Then

Sheets("sum").range("A1:D1").copy sheets("Sum").range("W3")

 

[GirishDhruva]

If it is for dynamic(means range might change like it will not always be A1:D1 and W3)

 

[GirishDhruva]

I tried and its working.

With Worksheets("Add_New_OR_Sum_Components")
      .Range("A1:S1").Copy
      r2.PasteSpecial
End With

Thanks for all your help @DanteAmor

Regards,
Dhruva.

 

[DanteAmor]

Again with pleasure. Thanks for the feedback