I can't figure out why these lines don't work

[JasonK]

Thanks for all your help. This group is awesome.


I can't figure out why these macro lines work:


Worksheets("DP").Range("b5:b54").Copy
Worksheets("Complete List").Range("a1:a50").PasteSpecial

and these, attempting the same exact job, don't:

Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy
Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _
1),Cells(ccc + 1 * 50, 1)).PasteSpecial

"i" is a variable in a for/next loop and in the current line
equals 2
"ccc" is a variable also that is currently equal to 0.


I keep getting an error message with the top line,
Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy

i dimensioned i and ccc as integers and both act as counters.


I'm trying to copy several columns of data from one sheet into one
large column of data into another sheet. I don't want to cut and
paste each individual column because eventually the sheets will change
and there will be more columns.

i must have some syntax incorrect.

thank you again,
JasonK

 

[Rick Rothstein]

Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy

Worksheets("Complete List").Range(Cells(ccc * 50 + 1, _
1),Cells(ccc + 1 * 50, 1)).PasteSpecial

**ALL** of the Cells reference are pointing to the ActiveSheet, not to the Worksheet references the Range properties that contain them are pointing to. You would need to add the correct worksheet reference for each Cells function call in order to guarantee they referred to the correct cell. For example, the last Cells reference.... Cells(ccc+1*50,1)... would need to become Worksheets("Complete List").Cells(ccc + 1 * 50, 1) in order to point to the worksheet I think you mean it to point to (and you would have to do the same thing for the other three of them).

 

[JasonK]

Rick,

I'm trying to copy and paste a range of cells, not just one cell.

worksheets("Complete List").cells(5,i), Cells(54,i).copy gagged.

i'm trying to copy the range of cells B5:B54 to another sheet A1:A50

Please take another look. The worksheets("Complete
List").Range(cells(a,a),cells(b,b)) works in other macros.

I don't understand why it wont work here.

JasonK

 

[OssieMac]

Hi Jason,

It's a trap for the uninitiated. You need to specify the worksheet name for
each of the cells like this. (Not required if activesheet)

Worksheets("DP").Range(Worksheets("DP").Cells(5, i), _
Worksheets("DP").Cells(54, i)).Copy

or better still use with (Note the . before each Cells tying them to the
worksheet.

With Worksheets("DP")
.Range(.Cells(5, i), .Cells(54, i)).Copy
End With

Also when pasting it is only necessary to identify the first cell and as you
are pasting all of the data in one column then you could use code something
like this.

Insert a column header of your choice before starting any copy/paste
operation with code like this.

With Worksheets("Complete List")
.Cells(1, 1) = "My Col Head"
End With

Use the following for the paste operation.

With Worksheets("Complete List")
.Cells(.Rows.Count, 1) _
.End(xlUp).Offset(1, 0).PasteSpecial
End With

 

[Rick Rothstein]

It probably works in other macros because you are not referencing cells on *other* worksheets. If you run a macro with Cells, Range, Columns, Rows property calls in it, they **automatically** reference the ActiveSheet **IF** you don't provide an explicit sheet reference. So, for this code that you posted originally, namely this...

Worksheets("DP").Range(Cells(5, i), Cells(54, i)).Copy Worksheets( _
"Complete List").Range(Cells(ccc * 50 + 1, 1), _
Cells(ccc + 1 * 50, 1)).PasteSpecial

....and assuming you ran it from the worksheet named "DP", Excel interprets it like this...

Worksheets("DP").Range(Worksheets("DP").Cells(5, i), Worksheets("DP"). _
Cells(54, i)).Copy Worksheets("Complete List"). _
Range(Worksheets("DP").Cells(ccc * 50 + 1, 1), _
Worksheets("DP").Cells(ccc + 1 * 50, 1)).PasteSpecial

In particular, notice this part of the code...

Worksheets("Complete List").Range( _
Worksheets("DP").Cells(ccc * 50 + 1, 1), _
Worksheets("DP").Cells(ccc + 1 * 50, 1))

Do you see how the Range property is referencing the worksheet named "Complete List" whereas the included Cells references are not... that is why your code line doesn't work. None of this is a problem if the code only references a single worksheet and, when you run the macro, that worksheet is the ActiveSheet.