If your not adding to pre-existing data on Sheet2, it might be faster to
duplicate the entire sheet1 then remove the unneccessary rows and add
whatever is neccessary for sheet2.

Either way, I ususally jot down sheet1's formating, row heights, etc and
re-apply once sheet2 is processed rather than copier which should also be
faster?

"DMB" <[email protected]> wrote in message
news:[email protected]...
> I need to make several copies of 64 rows from sheet 1 to sheet 2 including
> the formatting. the copy, rowheight, and the columnwidth functions are
> extremely slow. How would you do this with a collection object, or better
yet
> through the Excel database?
>
> Sub experiment()
> Dim NumberOfLines As Integer
> NumberOfLines = 3
> Dim ExistingSheet As Worksheet
> Dim NewSheet As Worksheet
> Set ExistingSheet = ThisWorkbook.Sheets("2")
> Set NewSheet = ThisWorkbook.Sheets("NewSheet")
>
> Dim i As Integer
> Dim j As Integer
> Dim LineCount As Integer
> For LineCount = 1 To NumberOfLines
> For i = 1 To 64
> For j = 1 To 13
> Worksheets("2").Cells(i + 10, j).Copy
> Destination:=Worksheets("NewSheet").Cells(i + 10 + (LineCount - 1) * 66,
j)
> Worksheets("2").Cells(i + 10, j).ColumnWidth =
> Worksheets("NewSheet").Cells(i + 10 + (LineCount - 1) * 66, j).ColumnWidth
> Next j
> Worksheets("2").Cells(i + 10, j).RowHeight =
> Worksheets("NewSheet").Cells(i + 10 + (LineCount - 1) * 66, j).RowHeight
> Next i
> Next LineCount
>
> End Sub