VBA Excel Range to cell

Question

I need to copy a range of cells into another range indicating the first cells. Something like that:

.Cells(1,1) = .Range("B1:B5")

Some help please, it doesn't work and I'm completely new in VBA Excel!

Could it be without usign copy functions? It's a really huge workbook and I need to copy a huge amount of data, and I've read that is no a good idea using this function. — Pablo
you could use 1) Set rng = .Range("B1:B5") and then 2) .Cells(1, 1).Resize(rng.Rows.Count, rng.Columns.Count).Value = .Range("B1:B5").Value. Or without defining new variable:.Cells(1, 1).Resize(.Range("B1:B5").Rows.Count, .Range("B1:B5").Columns.Count).Value = .Range("B1:B5").Value — Dmitry Pavliv

MattB MattB · Accepted Answer · 2014-02-27T23:09:59

I'm not sure what exactly you're trying to copy or to where, but...

With Workbooks("Book1").Worksheets("Sheet1")
    .Cells(1, 1).Value = .Range("B1").value
End With

will copy the contents of Cell B1 to Cell A1 on the same page. If you need to copy the 5 cells in the Range B1:B5 you need to specify a range as your first argument. You can do so as such:

.Range(.Cells(1, 1).Address & ":" & .Cells(5, 1).Address).value = .Range("B1:B5").value

Which will copy the contents of B1 to B5 over to A1 to A5.

Excel treats any number of cells inside a workbook be it 1 or 20 or more as a range, Cells lets you reference a single cell only. When you need to specify a range like Range("B1:B5") you can find the cells that define it and use .Address to get their string formatted range address. So Cells(1, 1).Address returns "$A$1" and conatenating these in a range reference will get you a range so long as you remember the ":" in the middle.

Hope that helps.

VBA Excel Range to cell

1 Answers