Automatically Copy data and move down cell

[iAmGreat]

Sub macro
Range("H21:L21").Select
Selection.AutoFill Destination:=Range("H21:L22"), Type:=xlFillDefault
End Sub


I have a pivot table and another table (which is not a pivot table) in the same sheet.

The other table is the one I used to create a graph.

I need a code where, when the pivot table updated, the other table will also update or autofill just like in the code above because there's a formula.

It should be a loop or continuous.

I hope you can help me.

Thank you so much in advance.

 

[L. Howard]

Hi iAmGreat, welcome to the board.

If I understand correctly, this may work for you.

Copy code to the sheet module with the table that is being updated. It relies on the cell F2 being a cell that will be updated and is NOT a formula, that is, there is some text or a value changed in that cell during the update. That change will fire the change-event macro and fill the range on Sheet4 with the formula shown.

You will need to change the red $F$2 to a cell that will be changed during the update.
And you will need to change the red formula "=(f10+f12)" to the formula that is in H21 on the "Other" sheet, shown as Sheet4, which you will also need to change.

So, change all the red's to match your sheets. Give it a go.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("[COLOR=#FF0000]$F$2[/COLOR]")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

  With Sheets("[COLOR=#FF0000]Sheet4[/COLOR]").Range("H21:L21")
    .Formula = "[COLOR=#FF0000]=(f10+f12)[/COLOR]"[COLOR=#FF0000][/COLOR][COLOR=#FF0000][/COLOR]
  End With
  
End Sub

 

[iAmGreat]

Hi Howard, thank you so much for your help.