Update data in one sheet with a button based on another workseet

[nondigital]

I have a worksheet COSTING in which data must be updated from another INPUT sheet every week. I need to have an UPDATE button on top of COSTING sheet so that upon clicking, it can pick data from the INPUT sheet.
COSTING sheet has subsets with subtotal on top of each subset. It looks like this:

[TABLE="width: 515"]
<tbody>[TR]
[TD="class: xl67, width: 103"]Element
[/TD]
[TD="class: xl67, width: 103"]Description[/TD]
[TD="class: xl67, width: 103, align: right"]Actual
[/TD]
[TD="class: xl67, width: 103, align: right"]Committed
[/TD]
[TD="class: xl67, width: 103, align: right"]Total
[/TD]
[/TR]
[TR]
[TD="class: xl65"]PC
[/TD]
[TD="class: xl65"]Main Costs[/TD]
[TD="class: xl65, align: right"]1984[/TD]
[TD="class: xl65, align: right"]196[/TD]
[TD="class: xl65, align: right"]2180[/TD]
[/TR]
[TR]
[TD="class: xl66"]PC-01[/TD]
[TD="class: xl66"]Equipments[/TD]
[TD="class: xl66, align: right"]342[/TD]
[TD="class: xl66, align: right"]43[/TD]
[TD="class: xl66, align: right"]385[/TD]
[/TR]
[TR]
[TD="class: xl66"]PC-02[/TD]
[TD="class: xl66"]Services[/TD]
[TD="class: xl66, align: right"]543[/TD]
[TD="class: xl66, align: right"]54[/TD]
[TD="class: xl66, align: right"]597[/TD]
[/TR]
[TR]
[TD="class: xl66"]PC-03[/TD]
[TD="class: xl66"]Commercial[/TD]
[TD="class: xl66, align: right"]223[/TD]
[TD="class: xl66, align: right"]22[/TD]
[TD="class: xl66, align: right"]245[/TD]
[/TR]
[TR]
[TD="class: xl66"]PC-04[/TD]
[TD="class: xl66"]Financial[/TD]
[TD="class: xl66, align: right"]876[/TD]
[TD="class: xl66, align: right"]77[/TD]
[TD="class: xl66, align: right"]953[/TD]
[/TR]
[TR]
[TD="class: xl65"]OH
[/TD]
[TD="class: xl65"]Overheads[/TD]
[TD="class: xl65, align: right"]99[/TD]
[TD="class: xl65, align: right"]99[/TD]
[TD="class: xl65, align: right"]198[/TD]
[/TR]
[TR]
[TD="class: xl66"]OH-1[/TD]
[TD="class: xl66"]Management[/TD]
[TD="class: xl66, align: right"]56[/TD]
[TD="class: xl66, align: right"]67[/TD]
[TD="class: xl66, align: right"]123[/TD]
[/TR]
[TR]
[TD="class: xl66"]OH-2[/TD]
[TD="class: xl66"]Admin[/TD]
[TD="class: xl66, align: right"]43[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]43[/TD]
[/TR]
[TR]
[TD="class: xl66"]OH-3[/TD]
[TD="class: xl66"]Selling[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]32[/TD]
[TD="class: xl66, align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]


the second sheet has the variable date which has same Element number. But there are blank rows and columns in between as it is coming from another program (SAP) It looks like this:

[TABLE="width: 698"]
<tbody>[TR]
[TD]S.No.[/TD]
[TD]Blank[/TD]
[TD]Element[/TD]
[TD]Nature[/TD]
[TD]Description[/TD]
[TD]Actual[/TD]
[TD]Committed[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]PC-01[/TD]
[TD]a[/TD]
[TD]Equipments[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]385[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]PC-02[/TD]
[TD]a[/TD]
[TD]Services[/TD]
[TD="align: right"]543[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]597[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]PC-03[/TD]
[TD]a[/TD]
[TD]Commercial[/TD]
[TD="align: right"]223[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]245[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]PC-04[/TD]
[TD]a[/TD]
[TD]Financial[/TD]
[TD="align: right"]876[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]953[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]OH-1[/TD]
[TD]b[/TD]
[TD]Management[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]OH-2[/TD]
[TD]b[/TD]
[TD]Admin[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]OH-3[/TD]
[TD]b[/TD]
[TD]Selling[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 301"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to the board.
Should the Costings sheet be overwritten every time, or added to?

 

[nondigital]

Thank you.
Yes. COSTING sheet should be overwritten every time as the second input sheet always has cumulative value.

 

[Fluff]

In that case give this a go

Sub nondigital()

    Dim InptSht As Worksheet
    Dim CstSht As Worksheet
    Dim PcRws As Long
    Dim OhRws As Long
    Dim UsdRws As Long

Application.ScreenUpdating = False
    Set InptSht = Worksheets("INPUT")
    Set CstSht = Worksheets("COSTINGS")
    
    InptSht.Activate
    UsdRws = Range("C" & Rows.Count).End(xlUp).Row
    PcRws = WorksheetFunction.CountIf(Columns(3), "PC*")
    OhRws = WorksheetFunction.CountIf(Columns(3), "OH*")

    With CstSht
        .Rows(2).Font.ColorIndex = 3
        .Range("A2").Value = "PC"
        .Range("B2").Value = "Main Costs"
        .Range("C2").FormulaR1C1 = "=sum(r[1]c:r[" & PcRws & "]c)"
        .Range("D2").FormulaR1C1 = "=sum(r[1]c:r[" & PcRws & "]c)"
        .Range("E2").FormulaR1C1 = "=sum(r[1]c:r[" & PcRws & "]c)"
    End With
    
    Range("A:B,D:D").EntireColumn.Hidden = True
    Cells.AutoFilter Field:=3, Criteria1:="=PC*", Operator:=xlAnd
    Range("A2:H" & UsdRws).SpecialCells(xlCellTypeVisible).Copy CstSht.Range("A3")

    With CstSht
        .Rows(PcRws + 3).Font.ColorIndex = 3
        .Range("A" & PcRws + 3).Value = "OH"
        .Range("B" & PcRws + 3).Value = "Overheads"
        .Range("C" & PcRws + 3).FormulaR1C1 = "=sum(r[1]c:r[" & OhRws & "]c)"
        .Range("D" & PcRws + 3).FormulaR1C1 = "=sum(r[1]c:r[" & OhRws & "]c)"
        .Range("E" & PcRws + 3).FormulaR1C1 = "=sum(r[1]c:r[" & OhRws & "]c)"
    End With

    Cells.AutoFilter Field:=3, Criteria1:="=OH*", Operator:=xlAnd
    Range("A2:H" & UsdRws).SpecialCells(xlCellTypeVisible).Copy CstSht.Range("A" & 4 + PcRws)
    Range("A:B,D:D").EntireColumn.Hidden = False
    Cells.AutoFilter

Application.ScreenUpdating = True

End Sub

 

[nondigital]

Didn't work

Run-time error '9':
Subscript out of range.

 

[Fluff]

What line did it fail on?