By user2597163

2013-07-18 20:44:42 8 Comments

I'm trying to move some data from one workbook into another by assigning the values from one range to another. When I use the normal Range syntax to specify the destination range (Range("A1:B2")) my code works, but if I try to use the Range, Cells syntax (Range(Cells(1,1),Cells(2,2))) my code doesn't work.

I activate the destination workbook (ActiveWorkbook) and have the code running in the source workbook (ThisWorkbook).

This code works:

ActiveWorkbook.Worksheets(1).Range("A1:B2").Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value

But This code does not:

ActiveWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value

The error I get is Run-time error '1004': Applicaton-defined or object-defined error.

Does anyone know why using the cells object is causing me problems, or if there is some other problem I'm not aware of?


@Dick Kusleika 2013-07-22 14:51:54

The problem is that Cells is unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you call Range or Cells or Rows or UsedRange or anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to:

  • In a sheet's class module: that sheet regardless of what's active
  • In any other module: the ActiveSheet

You qualify the Range reference, but the Cells reference is unqualified and is likely pointing to the Activesheet. It's like writing

ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value

which of course doesn't make any sense unless ThisWorkbook.Worksheets(1) happens to be active. I often like to use a With block so that I make sure everything is fully qualified.

With Sheets(1)
    .Range(.Cells(1,1), .Cells(2,2)).Value = "something"
End With

But you refer to two different sheets, so you'll be better off using short sheet variables like:

Dim shSource As Worksheet
Dim shDest As Worksheet

Set shSource = ThisWorkbook.Worksheets(1)
Set shDest = Workbooks("myBook").Worksheets(1)

shDest.Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value = _
    shSource.Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value

But really, if you're going to hardcode the Cells arguments, you could clean that up like

shDest.Cells(1, 1).Resize(2, 2).Value = shSource.Cells(1, 1).Resize(2, 2).Value

@dnLL 2014-08-07 19:38:49

This answer is the right one and should have been marked as so.

@h2so4 2017-04-22 10:23:07

no need to qualify the range in the instruction shDest.Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value = _ shSource.Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value this is also correct Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value= _ Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value

Related Questions

Sponsored Content

9 Answered Questions

2 Answered Questions

[SOLVED] VBA opening a few worksheets and copying cell value to the current

  • 2018-12-30 14:19:35
  • Wasteland
  • 28 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

1 Answered Questions

[SOLVED] Excel VBA inactive worksheet Range

1 Answered Questions

2 Answered Questions

[SOLVED] Why does define range with cell.address work but not cell only Excel VBA?

1 Answered Questions

2 Answered Questions

Trying to select with .Range( .Cells( #,#) ) won't work in VBA

2 Answered Questions

[SOLVED] VBA Excel: Assigning range values to a new range

1 Answered Questions

[SOLVED] sumifs using range cell - error 1004

Sponsored Content