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


--
Regards,

OssieMac


"JasonK" wrote:



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