VBA question - ranges

breadmonkey · 19-01-2014 2:28pm #1

Can anyone please explain why this works:

' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
    
[I]targetSheet.Range("A1", "C10").Value = sourceSheet.Range("A1", "C10").Value[/I]

but this doesn't:

' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
    
[I]targetSheet.Range(Cells(1, 1), Cells(10, 3)).Value = sourceSheet.Range(Cells(1, 1), Cells(10, 3)).Value[/I]

I've been googling for answers but I think my lack of understanding is so fundamental that I find it difficult to tell whether other similar sounding queries are relevant to my own.

Thanks

SimonTemplar · 19-01-2014 7:49pm

In the code that isn't working, the Cells object isn't prefaced by a worksheet. Therefore, it defaults to the currently active worksheet. Assuming the targetSheet isn't the active sheet, the first part of that line is actually saying:

"The values of cells A1:C10 of the active sheet in targetSheet"

This obviously makes no sense because cells can only be part of one sheet, therefore it throws an exception. The reason it works in the first snippet of code is because you are simply passing in a string to the range method of targetSheet (you are explicitly stating that the range belongs to targetSheet). That string will be resolved to a cell address. You are not dealing with a cell object and therefore it doesn't default to the active sheet.

To illustrate this, if you remove targetSheet and sourceSheet from the last line, everything will default to the active sheet, and therefore there is no conflict so it works.

To get your code working, you'll need to preface cells with targetSheet and sourceSheet just as you did with the range method.

targetSheet.Range(targetSheet.Cells(1, 1), targetSheet.Cells(10, 3)).Value = sourceSheet.Range(sourceSheet.Cells(1, 1), sourceSheet.Cells(10, 3)).Value

Hopefully I've explained this clearly.

breadmonkey · 20-01-2014 11:06pm

Thanks!

VBA question - ranges

Comments