Autofill formulas

[CP11]

I have the below VBA which:

1. Makes a copy of an existing sheet
2. Inserts extra columns
3. Adds column headers
4. Adds a formula to the new columns in row 2
5. Auto-fills the formula down to the last row (based on column A)
6. Repeats the above for a number of other columns.

It works, however takes quite a long time. My data set has thousands of row and 10 new columns to add and fill with formulas.

Is there a more efficient way to do this?

Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet2"

lastRow = Range("A" & Rows.Count).End(xlUp).Row


Columns("F:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Value = "Header 1"
Range("G1").Value = "Header2"

Range("F2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet3!C[-1],1,FALSE)"
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

Range("G2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet4!C[-2],1,FALSE),""N/A"")"
Range("G2").AutoFill Destination:=Range("G2:G" & lastRow)

'Repeat to add  X more new columns and formulas

 

[johnnyL]

You should probably post more of your code so we can get a better idea of what your code looks like.

 

[Peter_SSs]

It works, however takes quite a long time. My data set has thousands of row and 10 new columns to add and fill with formulas.

Is there a more efficient way to do this?

I suspect that, largely speaking, the time issue is likely to do with the time taken to calculate the results of all your new formulas rather than the efficiency of the code.

However, if your inserted columns are adjacent to each other like the sample, then you can at least deal with the whole group of columns together rather that each one individually.

So, instead of this

Range("F1").Value = "Header 1"
Range("G1").Value = "Header2"

Range("F2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet3!C[-1],1,FALSE)"
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

Range("G2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet4!C[-2],1,FALSE),""N/A"")"
Range("G2").AutoFill Destination:=Range("G2:G" & lastRow)

.. you can do this sort of thing.

Range("F1:G1").Value = Array("Header 1", "Header2")
Range("F2:G2").FormulaR1C1 = Array("=VLOOKUP(RC[-1],Sheet3!C[-1],1,FALSE)", "=IFERROR(VLOOKUP(RC[-2],Sheet4!C[-2],1,FALSE),""N/A"")")
Range("F2:G" & lastRow).FillDown