Range using cells is showing error

Question

I want to copy data from one worksheet to another (depending upon the number of Worksheets available), with the number of columns being dynamic.

If I use the code below it shows an Application object error:

Worksheets(1).Range(Cells(m, 2), Cells(m, 7)).Value = ThisWorkbook.Worksheets(i).Range(Cells(3, 2), Cells(3, 7)).Value

While if I use this:

Worksheets(1).Range(Cells(m, 2), Cells(m, 7)).Value = ThisWorkbook.Worksheets(i).Range("B2:G2").Value

it works fine, but I cannot make my columns dynamic.

These kind of questions have been asked SOOOOOOOO many times in SO.... I can point to at least 10 different such questions — Siddharth Rout, Jun 25 '17 at 07:15
@SiddharthRout **ALL** the VBA questions have been asked at least 10 times. The answers always boil down to "qualify your objects", "stop using Select and Activate", "declare your variables". — YowE3K, Jun 25 '17 at 08:10
@YowE3K: Not Lol :) But yes if you find any such question, feel free to close it or vote to close it as a duplicate. — Siddharth Rout, Jun 25 '17 at 08:15
@YowE3K: BTW you didn't have to delete your answer. Please undelete it. I have voted to undelete your answer :) — Siddharth Rout, Jun 25 '17 at 08:16
@SiddharthRout Wouldn't it be better to delete my answer so that future searchers follow the link to the original question. (The poster of this question has already taken my answer, so they are happy, and it's only the future searchers that we need to now be concerned with.) I've undeleted the answer for now, but will delete it again if you reconsider your last comment. — YowE3K, Jun 25 '17 at 08:31

score 2 · Answer 1 · answered Jun 25 '17 at 05:50

If you do not qualify your Cells references, they default to ActiveSheet.Cells. Thus your code

Worksheets(1).Range(Cells(m, 2), Cells(m, 7)).Value = _
     ThisWorkbook.Worksheets(i).Range(Cells(3, 2), Cells(3, 7)).Value

is equivalent to

Worksheets(1).Range(ActiveSheet.Cells(m, 2), ActiveSheet.Cells(m, 7)).Value =  _
     ThisWorkbook.Worksheets(i).Range(ActiveSheet.Cells(3, 2), ActiveSheet.Cells(3, 7)).Value

which obviously makes no sense.

You should use

Worksheets(1).Range(Worksheets(1).Cells(m, 2), Worksheets(1).Cells(m, 7)).Value = _
     ThisWorkbook.Worksheets(i).Range(ThisWorkbook.Worksheets(i).Cells(3, 2), ThisWorkbook.Worksheets(i).Cells(3, 7)).Value

This is easier to read if you set some Worksheet objects

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets(1)
Set ws2 = ThisWorkbook.Worksheets(i)

ws1.Range(ws1.Cells(m, 2), ws1.Cells(m, 7)).Value = _
     ws2.Range(ws2.Cells(3, 2), ws2.Cells(3, 7)).Value

Range using cells is showing error

1 Answers1