Hello anyone-that-can-help-me.


I am fairly new to macros within excel and have tried various differant

ways to get what i want to happen. I have some formulas which work
perfectly but i want to create a tracking spreadsheet with 5 tabs
consisting of (in the end) 65000 entries. If i copy the formulas down
(with an isblank(true) so it only shows me the ones with stuff in) the
file size is massive, so i tried to get a macro to put the formulas in
the row when something is put in collumn A , i got this to work but it
constantly refers to cell a2 for the answers (which is what the formula

states) because the macro wont recognise to update the formula for the
next line (which excel does by default), after it pastes the formula in

it then goes on to convert it to a value so all i am left with is the
answer (to save file size again) which is also working.


Its just getting it to refer to the next cell and so on and so on or
else it is a massive list of the same information!


I will paste the code below so you can see where i am, i am really
stumped now and dont know where to go next. please help me?


Private Sub Worksheet_Change(ByVal Target As Excel.range)


Application.EnableEvents = False


ActiveCell.Offset(-1, 1).range("A1").Select
ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,MID(A2,5,7))"


ActiveCell.Offset(0, 1).range("A1").Select
ActiveCell.Formula =
"=IF(A2=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(A2,12,2) ),Sheet2!A2:B43,2))"


ActiveCell.Offset(0, 1).range("A1").Select
ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,MID(A2,1,1)+2000)"


ActiveCell.Offset(0, 1).range("A1").Select
ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,datestamp())"


ActiveCell.Offset(1, 0).range("A1").Select


ActiveCell.Offset(0, -4).range("A1").Select


' these next () paragraphs are converting the calculated formulas to
values to save file size and
' also to stop the updation of the auto date


ActiveCell.Offset(-1, 1).range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 0).range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


ActiveCell.Offset(0, 1).range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 0).range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


ActiveCell.Offset(0, 1).range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 0).range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


ActiveCell.Offset(0, 1).range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 0).range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


ActiveCell.Offset(1, 0).range("A1").Select


ActiveCell.Offset(0, -4).range("A1").Select


Application.EnableEvents = True


End Sub