vba macro auto fill when connection updates

[dhughes85]

Hi all,

I have a sharepoint survey with a basic refresh macro I run once an hour to get new responses from. This is housed in Columns D-AL of an excel worksheek (it's a big survey)

Column D is the response date/time and in Column A-C I have the following

Time Day Week Column D
=d2 =weekday(d2,2) =weeknum(d2,2) sometime&somedate


What I want to do is create a macro that autofills the formulas from cells A-C when the data in column D grows (so if a refresh puts data into D3, cells A3-C3 autofill the formulas from A2-C2)

I was looking here and re-appropriated a macro from a query in another thread:



Sub autofill()
Dim MyCol As Long, MyRow As Long, LR As Long
MyCol = Cells.Find(What:="Week").Column
MyRow = Cells.Find(What:="Week").Row
LR = Cells(Rows.Count, MyCol - 1).End(xlUp).Row
Range(Cells(MyRow + 1, MyCol), Cells(LR, MyCol)).FormulaR1C1 = "=Weeknum(rc[1],2)"
MyCol = Cells.Find(What:="Day").Column
MyRow = Cells.Find(What:="Day").Row
LR = Cells(Rows.Count, MyCol - 1).End(xlUp).Row
Range(Cells(MyRow + 1, MyCol), Cells(LR, MyCol)).FormulaR1C1 = "=Weekday(rc[2],2)"
MyCol = Cells.Find(What:="Time").Column
MyRow = Cells.Find(What:="Time").Row
LR = Cells(Rows.Count, MyCol - 1).End(xlUp).Row
Range(Cells(MyRow + 1, MyCol), Cells(LR, MyCol)).FormulaR1C1 = "=rc[3]"
End Sub


which worked brilliantly to populate the existing data (down as far as row 5253...its a big survey with lots of responses :S) but hasn't grown, and isn't working, when I've refreshed the connection on column D.

What do I need to do to make it grow automatically?