Formula to be copies from sheet 16 onwards

[howard]

I have the following formula's on shets16 onwards in Col 1

 =IF(A25="","",VLOOKUP(A25,'New'!A:AL,COLUMNS($A:AL),FALSE))  

=IF(A100="","",VLOOKUP(A100,'Used'!A:AQ,COLUMNS($A:AQ),FALSE))

I would like a macro to insert the first formula in Col I row 25 and copy this up to row 98 from sheet16 to the last sheet


I would like a macro to insert the first formula in Col I in row 100 and copy this up to row 200 from sheet16 to the last sheet


Your assistance is most appreciated

 

[Yongle]

=IF(A25="","",VLOOKUP(A25,'New'!A:AL,COLUMNS($A:AL),FALSE))
I would like a macro to insert the formula in Col I row 25 and copy this up to row 98 from sheet16 to the last sheet

- test in a COPY of your workbook

Sub AddFormula()
    Dim aFormula As String, Rng As Range, Ws As Worksheet, x As Long
    aFormula = "=IF(A25="""","""",VLOOKUP(A25,'New'!A:AL,COLUMNS($A:AL),FALSE))"
'place formula in sheet16
    Set Rng = ThisWorkbook.Sheets("Sheet16").Range("I25")
    Rng.Formula = aFormula
'place formula in other sheets
    For x = Sheets("Sheet16").Index To ThisWorkbook.Worksheets.Count
        Sheets(x).Range("I25").Resize(74) = Rng.Formula
    Next
End Sub

=IF(A100="","",VLOOKUP(A100,'Used'!A:AQ,COLUMNS($A:AQ),FALSE))
I would like a macro to insert the formula in Col I in row 100 and copy this up to row 200 from sheet16 to the last sheet

- modify the above to match your 2nd formula and range

 

[Yongle]

note that I amended the above after initial posting to simplify the code

 

[howard]

Thanks for the help. code works perfectly. I will set up the code for the second formula

 

[Yongle]

The variable Ws is no longer being used in the code - it can be removed from declarations in first line

Thanks for the feedback

 

[howard]

thanks for letting me know. I have made the changes for the second formula in the code and it works perfectly