Excel VBA copying a static range into a dynamic range on different sheet

Question

I'm having a few issues working this out as I'm new to VBA but I'm sure it has a pretty simple solution.

I'm essentially wanting to automate the addition of new data to a sheet.

Sheet: INB BASKET Cells: A2:I76 contains live links to another worksheet. Basically I want to copy these as Values to Sheet IND TOTAL below the latest entry on a button press (not continually updating).

I've created a dynamic named range (PasteRange) in excel that selects the 75 rows below the last entry that I want to paste into:

=OFFSET('IND TOTAL'!$A$1,COUNTA('IND TOTAL'!$A:$A),0,75,9)

I've then created a module with the following:

Sub CopyRange()
Dim CopyFrom As Range

Set CopyFrom = Sheets("IND BASKET").Range("A2", [I76])
et PasteArea = Sheets("IND TOTAL").Range("PasteRange")

CopyFrom.Copy
PasteArea.PasteSpecial xlPasteValues
End Sub

But to no success as of yet, please advise.

Unknown Unknown · Accepted Answer · 2016-03-16T00:40:22

If you are only interested in pasting the values, then a direct value transfer is more efficient and does not involve the clipboard.

with Sheets("IND BASKET").Range("A2:I76")
    Sheets("IND TOTAL").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
end with

With your own Copy, Paste Special xlPasteValues you only need to specify the cell in the top-left corner of the destination and allow the copied area to define the size and shape.

Sheets("IND BASKET").Range("A2:I76").Copy
Sheets("IND TOTAL").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

The definition of your named range might be better without the volatile OFFSET function like,

=INDEX('IND TOTAL'!$A:$A, MATCH("zzz",'IND TOTAL'!$A:$A )+1):INDEX('IND TOTAL'!$I:$I, MATCH("zzz",'IND TOTAL'!$A:$A ) + 75)
'or for just the first cell
=INDEX('IND TOTAL'!$A:$A, MATCH("zzz",'IND TOTAL'!$A:$A )+1)

That assumes that column A contains text which I deduced by your use of COUNTA and not COUNT. If column A contains numbers, swap 1e99 for "zzz".

Excel VBA copying a static range into a dynamic range on different sheet

1 Answers