VBA code to fill formula down column

maclachlan

New Member
Joined
Dec 28, 2005
Messages
32
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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

New Member
Joined
Dec 28, 2005
Messages
32
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
try:
Worksheets("PivotTable").Range("E1").select
Worksheets("PivotTable").Range("E5:H5").Copy _
Destination:=Worksheets("PivotTable").Range("E6:H"&[D65536].End(xlUp).Row)
 

maclachlan

New Member
Joined
Dec 28, 2005
Messages
32
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top