AutoFill when new data added to sheet

[Endorphin]

Hi All !!!!!

OK, I have a Macro which imports data, and then adds a filtered version of that data to sheet B in columns A to E, this will happen on a weekly basis, is there a way to add predefined formulas to Columns F,G,H,I,J?? when the new data is added to the sheet?

Below is the code used to add the new data.

[vba]
Sub Move()
newrow = Sheets("Stored Data").Cells(60000, 1).End(xlUp).Row + 1
lastrow = Sheets("Temp").Cells(60000, 1).End(xlUp).Row
firstrow = Sheets("Temp").Cells(lastrow, 1).End(xlUp).Row
colA = Sheets("Temp").Cells(firstrow, 1)
For i = 2 To newrow - 1
If Sheets("Stored Data").Cells(i, 1) = colA Then MsgBox ("This Data has already been imported. Muppet!!")
If Sheets("Stored Data").Cells(i, 1) = colA Then GoTo done
Next i
Sheets("Temp").Range(Cells(firstrow, 1), Cells(lastrow, 5)).Copy _
Sheets("Stored Data").Cells(newrow, 1)
done:
End Sub
[/vba]

[Bob Phillips]

Something along the lines of

[vba]

Sheets("Stored Data").Range("F2").Resize(newrow - 1).Formula = "=the_formula"
'etc.
[/vba]

[Endorphin]

Hi XLD, and thanks for your help

It works but only fills one cell F2", it is adding 500 new rows, any suggestions on how to get around this?

[Endorphin]

OK, added this to the bottom, Is there a better way to do this as my VBA is trial and error.... mostly error...

[vba]
For i = firstrow To lastrow
Sheets("Stored Data").Cells(newrow, 6).Formula = "the_formula"
newrow = newrow + 1
Next i
[/vba]

[Endorphin]

OK, Thanks to XLDs direction the end code looked like this, probably not the best way to do it but it works.... which for me is amazing!!!

[vba]
Sub Move()
newrow = Sheets("Stored Data").Cells(60000, 1).End(xlUp).Row + 1
lastrow = Sheets("Temp").Cells(60000, 1).End(xlUp).Row
firstrow = Sheets("Temp").Cells(lastrow, 1).End(xlUp).Row
colA = Sheets("Temp").Cells(firstrow, 1)
For i = 2 To newrow - 1
If Sheets("Stored Data").Cells(i, 1) = colA Then MsgBox ("This Data has already been imported. Muppet!!")
If Sheets("Stored Data").Cells(i, 1) = colA Then GoTo done
Next i
Sheets("Temp").Range(Cells(firstrow, 1), Cells(lastrow, 5)).Copy Sheets("Stored Data").Cells(newrow, 1)
For i = firstrow To lastrow
Sheets("Stored Data").Range("F1").Copy Sheets("Stored Data").Cells(newrow, 6)
newrow = newrow + 1
Next i
done:
End Sub
[/vba]

[Bob Phillips]

This fills down for me

[vba]

newrow = Sheets("Stored Data").Cells(60000, 1).End(xlUp).Row + 1
LastRow = Sheets("Temp").Cells(60000, 1).End(xlUp).Row
firstrow = Sheets("Temp").Cells(LastRow, 1).End(xlUp).Row

colA = Sheets("Temp").Cells(firstrow, 1)
For i = 2 To newrow - 1
If Sheets("Stored Data").Cells(i, 1) = colA Then
MsgBox ("This Data has already been imported. Muppet!!")
GoTo done
End If
Next i
With Sheets("Temp")
.Range(.Cells(firstrow, 1), .Cells(LastRow, 5)).Copy Sheets("Stored Data").Cells(newrow, 1)
End With
Sheets("Stored Data").Cells(newrow, 6).Resize(LastRow - firstrow + 1).Formula = "the_formula_2"
[/vba]

[Endorphin]

Thats Great, seems to work alot faster than mine. Many thanks again XLD