Copy formulas down vba

[MetLife]

Hi,

I have a macro that grabs data from a file, this is a table of 25x150 rows. The rows can vary.

So in my other tab I have formulas that start on Cell A5 and these reference the data in Tab #1.

All I want to do is have the macro copy the formulas in cell A5 down to as many records as was in the file of 25x150 rows. Since the rows varies, what is the best way to do this?

I was thinking of just having a formula "=Counta(A:A)" to count the records and then use offset, but there is probably a better way.

Thanks,

 

[dreid1011]

What are the formulas in A5 on the second sheet?

 

[MetLife]

A5 = Sheet1!A5

 

[dreid1011]

A5 = Sheet1!A5

Is it the same for all the columns too?

 

[MetLife]

Is it the same for all the columns too?

No some formulas say things like B5 = VLOOKUP(A1, Sheet1!A5:D100,10,false)

 

[dreid1011]

So do you want the formula to only affect col A?

 

[MetLife]

So do you want the formula to only affect col A?

I want to copy the entire row of formulas down. A5:X100

 

[dreid1011]

You want it to affect the formulas in all columns from A:X? Being that they are all different formulas, you may need VBA to copy them down. The following will work for just col A though:

=Sheet1!A5:INDEX(Sheet1!A:A,XMATCH(FALSE,ISBLANK(Sheet1!A:A),0,-1))

Copy and paste into A5, do not copy down.

 

[MetLife]

You want it to affect the formulas in all columns from A:X? Being that they are all different formulas, you may need VBA to copy them down. The following will work for just col A though:

=Sheet1!A5:INDEX(Sheet1!A:A,XMATCH(FALSE,ISBLANK(Sheet1!A:A),0,-1))

Copy and paste into A5, do not copy down.

I want to do this in vba

 

[Alex Blakenburg]

Your formulas in row 5 would need be such that copying them down won't impact the references ie your VLookup "B5 = VLOOKUP(A1, Sheet1!A5:D100,10,false)" won't be correct if copied down.

Let's start with this:

Sub CopyFormulas()

    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim rowLastSrc As Long, colLastSrc As Long
    Dim rngSrc As Range, rngDest As Range
    
    Set shtSrc = Worksheets("Sheet1")
    Set shtDest = Worksheets("Sheet2")
    
    With shtSrc
        rowLastSrc = .Cells(Rows.Count, "A").End(xlUp).Row
        colLastSrc = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rngSrc = .Range(.Cells(5, "A"), .Cells(rowLastSrc, colLastSrc))
    End With
    
    With shtDest
        Set rngDest = .Range("A5").Resize(rngSrc.Rows.Count, rngSrc.Columns.Count)
        rngDest.Rows(1).AutoFill Destination:=rngDest
    End With

End Sub