PDA

View Full Version : AutoFill when new data added to sheet


Endorphin
12-15-2008, 05:23 PM
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.


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
Bob Phillips
12-15-2008, 05:27 PM
Something along the lines of



Sheets("Stored Data").Range("F2").Resize(newrow - 1).Formula = "=the_formula"
'etc.
Endorphin
12-15-2008, 05:50 PM
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
12-15-2008, 06:21 PM
OK, added this to the bottom, Is there a better way to do this as my VBA is trial and error.... mostly error...


For i = firstrow To lastrow
Sheets("Stored Data").Cells(newrow, 6).Formula = "the_formula"
newrow = newrow + 1
Next i
Endorphin
12-15-2008, 07:09 PM
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!!!


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
Bob Phillips
12-16-2008, 01:28 AM
This fills down for me



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"
Endorphin
12-16-2008, 03:27 PM
Thats Great, seems to work alot faster than mine. Many thanks again XLD