0

I seem to be getting an error on this, and I do not understand why, I know I can just use Range with letters but I want to learn how to do it in this format.

 ThisWorkbook.Sheets("t").Range(Cells(1, 1), Cells(2, 2)).Value = ThisWorkbook.Sheets("1").Range(Cells(1, 1), Cells(2, 2)).Value
CodeCamper
  • 6,609
  • 6
  • 44
  • 94
  • What error are you getting? – Amit Jun 01 '15 at 17:33
  • @Amit Application-Defined or object-defined error I am using Excel 2013, it is really strange because if I replace it with 1 cell with works or if I just use an alphanumeric range it works. – CodeCamper Jun 01 '15 at 17:37
  • 1
    When you use Cells(1, 1) you're implicitly using ActiveSheet.Cells(1, 1). You have to qualify it. – Sobigen Jun 01 '15 at 17:37
  • @Sobigen what would be the shortest way to accomplish what I am trying to do? – CodeCamper Jun 01 '15 at 17:39
  • 1
    As Sobigen said, you have to qualify the Cells() methods you're calling in the Range() method. See this post for details: http://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why/18962945#18962945 – tigeravatar Jun 01 '15 at 17:39

2 Answers2

2

Here's my test code, I added some worksheet objects to make it a read a little easier. You have to qualify the Range too if working from a worksheet module, if you're in a plain module you can leave it out.

sub test()
    dim t as worksheet
    dim one as worksheet

    set t = ThisWorkbook.Sheets("t")
    set one = ThisWorkbook.Sheets("1")

    t.Range(t.Cells(1, 1), t.Cells(2, 2)).Value = one.Range(one.Cells(1, 1), one.Cells(2, 2)).Value

    'Alternatively, what Jeeped is referencing in his comment:
    with ThisWorkbook.Sheets("t")
        .range(.cells(1, 1), .cells(2, 2).value = one.Range(one.cells(1, 1), one .Cells(2,2)).value
    end with
end sub
Sobigen
  • 2,038
  • 15
  • 23
  • A [With...End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx) could elimiate at least one of the worksheet references. –  Jun 01 '15 at 18:08
0

The answer by @Sobigen gives a good way to qualify your references to avoid the error.

You can also avoid Cells altogether by using Resize.

Sub UseResizeInsteadOfCells()

    ThisWorkbook.Sheets("t").Range("A1").Resize(2, 2).Value = _
        ThisWorkbook.Sheets("1").Range("A1").Resize(2, 2).Value

End Sub

I used A1 since you are doing Cells(1,1) on a Worksheet which is the same reference. You could also use .Cells(1,1).Resize(2,2) and get the same result without worrying about qualifying references inside a Range call.

Byron Wall
  • 3,970
  • 2
  • 13
  • 29