Copying one range to another vba

[dan_l]

What oh what am I missing here:


Dim sourceh As Integer

Dim sourcew As Integer

sourceh = Range("chartsource").Rows.Count

sourcew = Range("chartsource").Columns.Count

Sheets("test").Range(Cells(1, 1), Cells(sourceh, sourcew)).Value = Sheets("sheet2").Range("chartsource").Value


So on chart click vba will:

1. determine the size of the source data (this isn't very slick, it's just a named range called 'chart source')

2. Copy the range to an equally sized range on another sheet


This works when the destination sheet is active(in this case, test) but throws a error 1004 any other time.


What did I miss?

 

[VaraK]

Hi dan_I,


Try selecting Sheets("test") before equalizing the range. Something like this.


Dim sourceh As Integer

Dim sourcew As Integer

sourceh = Range("chartsource").Rows.Count

sourcew = Range("chartsource").Columns.Count

Sheets("test").Select

Sheets("test").Range(Cells(1, 1), Cells(sourceh, sourcew)).Value = Sheets("sheet2").Range("chartsource").Value


Hope this helps.

 

[dan_l]

Is that a requirement?

 

[VaraK]

I faced this problem sometime ago when I was writing code for someone. When we hardcode the range like Range("A1:B3") it doesn't throw up the error. But if you want to refer the range using cells like Range(Cells(1, 1), Cells(sourceh, sourcew))then it throws up this error (Appliaction defined-error).


Finally when I selected the sheet and run the code it started working.

 

[Luke M]

To expand:

You used:

Sheets("test").Range(Cells(1, 1), Cells(sourceh, sourcew)).Value


The problem is the Cells object doesn't reference the test sheet (only the range object is connected). If no sheet is specified, the object uses the default current selected sheet (for any VB purists, I apologize if my vocab is wrong, I'm self-taught). To correct, w/o having to select the sheet, you would need to write:

Sheets("test").Range(Range("test").Cells(1, 1), Range("test").Cells(sourceh, sourcew)).Value


Note how everything is tied back to correct sheet now. In VaraK's example, the Range is already connected to sheet test, so there was no problem. Hope that clarifies things a bit.