Excel VBA - Paste formula to sheet2 based of sheet1

Albert Mulder from

So I have sheet1 with data that get put into it, (the amount of rows changes each time we get the data)

I need to take info from particular columns on sheet1 and copy the info to sheet2.

Starting at row 2 on Sheet2 should look like this:

    Column A =VALUE('Sheet1'!AK2)
    Column B =VALUE('Sheet1'!G2)
    Column C =VALUE('Sheet1'!F2)
    Column D =VALUE('Sheet1'!Q2)
    Column E =VALUE('Sheet1'!R2)
    Column F =VALUE('Sheet1'!S2)
    Column G =VALUE('Sheet1'!T2)
    Column H =VALUE('Sheet1'!U2)

With each row increasing (AK3, AK4 ect..) until all rows from sheet1 are copied, I can't have it copy empty rows at the end this is why I need VBA to enter in the formulas for me.

I copy data from SQL and past it into sheet1 need a macro that puts formulas in fields that I need, but have no clue how to do that.

Thanks for your help.

excel excel-vba paste


answered 2 months ago Jeeped #1


Option Explicit

Sub Macro10()
    Dim arr As Variant, i As Long, lr As Long

    arr = Array(37, 7, 6, 17, 18, 19, 20, 21)

    With Worksheets("sheet2")
        For i = 1 To 8
            lr = Worksheets("sheet1").Cells(.Rows.Count, arr(i - 1)).End(xlUp).Row
            lr = application.max(lr, 2)
            .Range(.Cells(2, i), .Cells(lr, i)).Formula = _
                "=value(" & Worksheets("sheet1").Cells(2, arr(i - 1)).Address(0, 0, external:=True) & ")"
        Next i
    End With

End Sub