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.

--
Rick (MVP - Excel)


"JasonK" wrote in message ...

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




On Thu, 23 Jul 2009 03:24:46 -0400, "Rick Rothstein"
wrote:

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).