Drag formula and paste as values - VBA

[jwb1012]

Hello, I am using the following code to copy formulas down multiple rows (including array formulas that need to be "dragged down") based on a number in column A (ins). Every time there is a number in column 8 (ex: 5) the model will insert 5 rows and drag the formulas in that row (column A:U) down that many times.

Now.. I am struggling to "paste as values" - after I copy/drag the formulas down. Any thoughts on how to add a few lines of code to paste as values?

 

[igold]

You could put the column into an array and then put the array back to the sheet and it will go back as values not formulas...

 

[jwb1012]

Can you please elaborate on this a bit? I am fairly new to VBA and am not sure I understand how to implement this technique.

 

[igold]

What is the range that you want to act upon...

 

[jwb1012]

Right now, I have multiple summary tables where I've used the array formula under the "Resource Description" column header. The Resource Description column headers are currently in cells C14, C22, C27 so the formulas are currently in C15, C21, C26 etc. - because there are multiple summary tables.

{=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($T24='Staffing Plan'!$Y$14:$Y$1008, COUNTIF($C$23:$C23, 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")}

Then I use this cell to drag the formulas in cells A:U of that row down. It extracts a unique list of "Resource Descriptions" from column K of the Staffing Plan worksheet based on the Project Number in column Y of the Staffing Plan that matches cell A1.

Sub Insert_Rows()
    Dim Sh As Worksheet
    Dim End_Row As Long
    Dim N As Long
    Dim Ins As Long
    
        For Each Sh In ActiveWorkbook.Sheets
            If Left(Sh.Name, 9) = "Labor BOE" Then
                
                End_Row = Sh.Range("T" & Rows.Count).End(xlUp).Row
                
                For N = End_Row To 3 Step -1
                    Ins = Sh.Cells(N, "A").Value
                    
                    If Ins > 0 Then
                        Sh.Range("A" & N + 1 & ":A" & N + Ins).EntireRow.Insert
                        Sh.Range("A" & N & ":U" & N).Copy Destination:=Sh.Range("A" & N + 1 & ":U" & N + Ins)
                    End If
                Next N
                
            End If
        Next Sh
End Sub

 

[jwb1012]

The problem is that this method seems to be very very slow. Maybe if it was possible to paste the formulas as values after they are copied/dragged down it would speed things up (i.e less array formulas calculating over and over) or maybe there is a different way to extract the unique list of resource IDs through code?

 

[igold]

Hi,

I am not sure what your exact range(s) are, as I have not really looked at your code. That being said the code below would take as an example, Range("A4:U29"), and write the values that reside in that range, to array "arr" and then write those values back as values. If there were any formulas in that range they would now be values. If that makes any sense...

Sub test()


    Dim arr
    arr = Range("A4:U29")
    Range("A4").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr


End Sub

I hope this helps.

igold