VBA macro to paste from column range to multiple ranges

Question

I'm trying to copy a formula from a range in column F and paste into each column to the last column in the range.

When pasting to the range, copy cells are only pasted to column F instead of the selected range.

Code:

Dim lastrow As Long
Dim lastcol As Long
Dim i As Long
Range("f2:f" & lastrow).Select
Selection.Copy
lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
Range("f2:f" & lastrow).Select
With ActiveCell
    .Resize(lastrow, lastcol - 6).Select
End With

Selection.PasteSpecial xlPasteFormulas

Do you have On Error Resume Next somewhere in your code? What is the value of lastrow? — Vityata

Michał Turczyn Michał Turczyn · Accepted Answer · 2018-04-17T11:09:28

First of all, you lastrow isn't set! You have to assign it a value, like this:

lastrow = Cells(Rows.Count, 6).End(xlUp).Row

This will find the last row in F column.

Now, use just Range("f2:f" & lastrow).Copy to avoid Select (which is advised).

To paste it to last column, you have to do it like this:

Range(Cells(2, lastcol), Cells(lastrow, lastcol).PasteSpecial xlPasteFormulas

Putting it all together, use this code instead:

Option Explicit
Sub CopyRange()
    'use camel case or underscores for better readability
    Dim lastRow As Long, lastCol As Long
    lastRow = Cells(Rows.Count, 6).End(xlUp).Row
    'I changed the row here to second row (instead of 6th)
    lastCol = Cells(2, Columns.Count).End(xlToLeft).Column

    Range("f2:f" & lastrow).Copy
    Range(Cells(2, lastCol), Cells(lastRow, lastCol).PasteSpecial xlPasteFormulas
End Sub

Additional note: I wrapped it in a Sub and used Option Explicit, which is also advised for avoiding run-time errors.

VBA macro to paste from column range to multiple ranges

1 Answers