Automatic Copying of Cells to Another Cells with Macro

[BobPierre]

Hi Everyone,
I am having an issue with using Macro. In fact I had never used it before.
I am having something like this:
A B C D E F
50Km Dispatches 1am 2am 3am
WEST 64 173
EAST 36 203
NORTH 1 37

I want my Macro to be picking values from B2 to B4 and place it in D2 to D4 respectively anytime I update my data and refresh my Pivot table While the next time I update it, it will pick values from B2 to B4 and place it in E2 to E4, etc

I have been trying this but it is not working:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
ActiveSheet.Range("c2:c4").Copy
Sheets("Distance Report").Range("IV1").End(xlToLeft).Offset(1, 1).PasteSpecial xlValues
Application.CutCopyMode = False


End Sub


The issue is that it only works once, it wont work for subsequent update

 

[rpaulson]

maybe change

Sheets("Distance Report").Range("IV1").End(xlToLeft).Offset(1, 1).PasteSpecial xlValues

to
Sheets("Distance Report").Range("IV2").End(xlToLeft).Offset(1, 1).PasteSpecial xlValues

 

[BobPierre]

Thanks for your response @rpaulson.
I have tried it but when I clicked on refresh all, it pastes it twice instead of once, although this what I used:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
ActiveSheet.Range("c2:c4").Copy
Sheets("Distance Report").Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False