Copy/Paste Excel VBA using Array

[zg825]

Hello,

I am trying to copy about 100 formulas from a row, and then paste them 2 rows below where I copied, and then have it filldown til the last row.The issue is that the 100 formulas are not in the immediate columns, it can go A4 - F4, and then M4-N4, P4-T4... and so on.

Here is what I have started thus far:

Sub Paste_Data()
    Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer
    With Sheets("AssetPlanQry")
        lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
        arr1 = Array("B4", "F4", "L4", "M4", "N4", "O4", "P4", "Q4", "R4", "S4", "T4", "V4", "W4", "X4", "Y4", "Z4", "AA4", "AB4", "AC4", "AD4", "AE4")
        arr2 = Array("B6", "F6", "L6", "M6", "N6", "O6", "P6", "Q6", "R6", "S6", "T6", "V6", "W6", "X6", "Y6", "Z6", "AA6", "AB6", "AC6", "AD6", "AE6")
)
For i = LBound(arr1) To UBound(arr1)
    With Sheets("AssetPLanQry")
        
Range("B4").Copy
Range("B6").PasteSpecial
Range("B6:B" & lastrow).FillDown

End Sub

I was trying to get help so that instead of it saying Range(B4), it would go to the first item in the array, then do the paste for 2nd array Range(b6), and then filldown based off 2nd array Range(b6)

I appreciate any help someone can give,

Zach

 

[JLGWhiz]

Try changing this"

Range("B4").Copy
Range("B6").PasteSpecial

to this:

,Range(arr1(i)).Copy
,Range(arr2(i)).PasteSpecial xlPasteFormulas
End With
Next

 

[zg825]

Thanks, that worked great for that part. Do you know how to get it to next filldown for each of the Column in the array2 going to the last used row. For instance going from B6 to lastrowDB.

Sub Paste_Data()
    Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer
    With Sheets("AssetPlanQry")
        lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
        arr1 = Array("B4", "F4", "L4", "M4", "N4", "O4", "P4", "Q4", "R4", "S4", "T4", "V4", "W4", "X4", "Y4", "Z4", "AA4", "AB4", "AC4", "AD4", "AE4")
        arr2 = Array("B6", "F6", "L6", "M6", "N6", "O6", "P6", "Q6", "R6", "S6", "T6", "V6", "W6", "X6", "Y6", "Z6", "AA6", "AB6", "AC6", "AD6", "AE6")
For i = LBound(arr1) To UBound(arr1)
    With Sheets("AssetPLanQry")
Range(arr1(i)).Copy
Range(arr2(i)).PasteSpecial xlPasteFormulas
'Range(arr2(i)).FillDown ' Needs to go to last row

End With
Next

 

[shg]

Maybe (untested),

Sub Paste_Data()
  Dim asAddr()      As String
  Dim r             As Range
  Dim i             As Long

  asAddr = Split("B4 F4 L4 M4 N4 O4 P4 Q4 R4 S4 T4 V4 W4 X4 Y4 Z4 AA4 AB4 AC4 AD4 AE4")

  With Worksheets("AssetPlanQry")
    Set r = .Range("A6", .Cells(.Rows.Count, "A").End(xlUp)).EntireRow

    For i = 0 To UBound(asAddr)
      With .Range(asAddr(i))
        .Copy Intersect(.EntireColumn, r)
      End With
    Next i
  End With
End Sub

 

[zg825]

Thanks SHG. that worked well. Is there a limit to the amount of columns i can have in the AsAddr = Split() ?

 

[shg]

Only the limitation of line length.