VBA code to fill formula down column

[maclachlan]

I have a pivot table that uses columns A to D. I have added a button using code that updates the pivot table. Columns E and F contain a vlookup formula that is based on column D of the pivot table. The pivot table will grow and shrink upon updating and I need the formula to do the same.
I think there must be a way using some code to look and see if the cell in column D contains data then the formula is copied to columns E and F. I would like the formula to copy down the entire column each time the pivot table is updated. This will make sure a fresh formula is created each time.

Thanks
Rob

 

[tactps]

You need a macro (or add this into yours), e.g. let's say your first formula is in cell e4

Sub macro()
Range("E4:F4").Copy
Range("E5:F" & [d65536].End(xlUp).Row).Select
ActiveSheet.Paste
End Sub

 

[maclachlan]

Thanks for the reply. When I use the code I get the following error:
Run-time error '1004':
Select method of Range class failed


Here is my code
Private Sub CommandButton3_Click()


Worksheets("GMAC").Range("D2:D5000").Copy _
Destination:=Worksheets("Data1").Range("A2")
Worksheets("GMAC").Range("J2:J5000").Copy _
Destination:=Worksheets("Data1").Range("B2")
Worksheets("GL").Range("D2:D5000").Copy _
Destination:=Worksheets("Data1").Range("A5001")
Worksheets("GL").Range("C2:C5000").Copy _
Destination:=Worksheets("Data1").Range("C5001")


Worksheets("PivotTable").PivotTables("PivotTable3").PivotCache.Refresh

Sheets("PivotTable").Select
Range("E5:H5").Copy
Range("E6:H" & [D65536].End(xlUp).Row).Select
ActiveSheet.Paste


End Sub

 

[tactps]

try:
Worksheets("PivotTable").Range("E1").select
Worksheets("PivotTable").Range("E5:H5").Copy _
Destination:=Worksheets("PivotTable").Range("E6:H"&[D65536].End(xlUp).Row)

 

[maclachlan]

I tried this code but get the following error:
Run-time error '1004'
Select method of range class failed

Worksheets("PivotTable").Range("E1").Select
Worksheets("PivotTable").Range("E5:H5").Copy _
Destination:=Worksheets("PivotTable").Range("E6:H" & [D65536].End(xlUp).Row)

Thanks
Rob