Excel 2007 Range and Cell Syntax issue

Question

I am trying to code a loop that takes each row from a page of the main worksheet, and places each in a corresponding worksheet. For instance, row A1:A10 on worksheet one would be copied and placed on A1:A10 on worksheet 2. And on worksheet 3, the A1:A10 Values would be from worksheet one values A2:A10. But my problem is, I can't find the right syntax to loop through my loop using the Range and Cell functions.

My Code:

'i is set to the first worksheet I am writting to
'wsCount is set to the last worksheet I want to write to
For i = 2 To wsCount
    Worksheets(i).Activate
    'This code shifts old information down so the most recent is always on top
    Worksheets(i).Range("A3:L262").Select
    Selection.Copy
    Worksheets(i).Range("A4").Select
    ActiveSheet.Paste
    'This sets the first cell in the row to be the current date
    Worksheets(i).Range("A3").Value = Format(Now, "ddd, d mmmm yyyy")

    'This is my problem, I am trying to set the range 
    'B3:L12 to the corresponding row in the main worksheet
    Worksheets(i).Range(Cells(3, 2), Cells(3, 12)).Value = Worksheets(1).Range(Cells(x, 2), Cells(x, 12)).Value

    Worksheets(i).Range("A1").Select
    x = x + 1

Next i

This error throws "Run-time error '1004': Application-defined or object-defined error"

I feel like my syntax is correct, because when I run:

Worksheets(1).Range(Cells(10, 2), Cells(10, 12)).Value = "Test"

It runs fine, but only when the worksheet(1) is selected. So I know that I need to select the first worksheet to pull information from it, to the other worksheets or something like that. What am I missing? Does it have something to do with my "Worksheets(i).Activate" statement at the beginning of the loop? I am new to VBA and am not sure about the permissions for Excel VBA macros.

@chrisneilsen Thank you! A very informative post on the correct way I should code this. — Tyler Jones, Jan 20 '15 at 22:49

score 0 · Answer 1 · answered Jan 18 '15 at 01:01

0

Just add x=1 before the For Next structure.

x=1
For i = 2 To wsCount (...)

When the code runs, it tries to select the cell(0,2) but there is no row=0 (x=0). I believe this the solution.

answered Jan 18 '15 at 01:01

Felipe

163
3
7
21

Thank you for the catch! Sloopily on my part, I forgot to add the line of code were I initially gave x a value of the first row of data. – Tyler Jones Jan 20 '15 at 22:48

score 0 · Accepted Answer · answered Jan 18 '15 at 02:45

In this code:

Worksheets(1).Range(Cells(10, 2), Cells(10, 12)).Value = "Test"

the Range method applies to Worksheets(1) but the Cells methods apply to the active sheet. That code is equivalent to:

Worksheets(1).Range(ActiveSheet.Cells(10, 2), ActiveSheet.Cells(10, 12)).Value = "Test"

If you were just using a fixed reference then you could use the version of the Range method which takes a string argument:

Worksheets(1).Range("B10:L10")

If you need a reference which changes depending on the value of a variable then you can use a With ... End With block:

With Worksheets(1)
  .Range(.Cells(x, 2), .Cells(x, 12)).Value
End With

where the unqualified references that start with a . refer to the object specified in the With ... End With block.

In your code which isn't working, you need to refer to two different worksheets in the same line so With ... End With isn't enough on its own. Using variables to refer to each worksheet is probably the clearest way to get this done. Before the For ... Next loop starts, declare two worksheet variables:

Dim wsFirst As Worksheet
Dim wsCurr As Worksheet

and then go with:

Set wsFirst = Worksheets(1)

For i = 2 To wsCount
  Set wsCurr = Worksheets(i)

Your problem line then becomes:

wsCurr.Range(wsCurr.Cells(3, 2), wsCurr.Cells(3, 12)).Value = wsFirst.Range(wsFirst.Cells(x, 2), wsFirst.Cells(x, 12)).Value

You should also replace the other references to Worksheets(1) and Worksheets(i) with wsFirst and wsCurr respectively.

As pointed out in the comments to the question, you can also remove all of the selecting and activating which will make your code more efficient and stop it being reliant on which sheet happens to be active when the code runs.

Thank you @barrowc! I can see how my code was throwing errors now. Thank you especially for the coding examples in your answer, they helped a lot! — Tyler Jones, Jan 20 '15 at 22:53

score 0 · Answer 3 · answered Jan 19 '15 at 16:01

Just change

Worksheets(i).Range(Cells(3, 2), Cells(3, 12)).Value = Worksheets(1).Range(Cells(x, 2), Cells(x, 12)).Value

to

For Each cell In Worksheets(i).Range(Cells(3, 2), Cells(3, 12))
    Worksheets(1).Cells(x, cell.Column).Value = cell.Value
Next cell

And give an initial value to x

Excel 2007 Range and Cell Syntax issue

3 Answers3