hi Howard,

if i have good understand

Sub ThreeHund()
Dim iColumn As Integer
Dim wks1 As Workbook, wks2 As Workbook
Dim lLast As Long
Dim i As Long, j As Long

Set wks1 = Worksheets("Dum")
Set wks2 = Worksheets("Mud")

lLast = wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row

For iColumn = 2 To 3
lLast = Application.Max(lLast, wks1.Cells(wks1.Rows.Count,
iColumn).End(xlUp).Row)
Next iColumn

If lLast < 300 Then
MsgBox "Less than 300 rows", vbOKOnly
Exit Sub
End If

For i = 300 To lLast Step 300
j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1
wks1.Range("A" & i & ":C" & i + 299).Copy wks2.Cells(1, j)
Next
End Sub

isabelle

Le 2013-02-19 18:13, Howard a écrit :

Hi Excel Experts,

Sheet "Dum" A, B, C by 1200 rows.
Sheet "Mud" blank.

Trying to cut the bottom 300 rows of A, B, C from "Dum" to A1, B1, C1 in "Mud".
Next bottom 300 from "Dum" to D1, E1, F1 in "Mud" & repeat for a total of 5 times, hence the For i = 1 to 5 - Next.
I get the first bottom 300 from "Dum" to "Mud" correctly and then its 'game over'. If it does one, why not 5?

Option Explicit

Sub ThreeHund()

Dim iColumn As Integer
Dim lLast As Long
Dim i As Long, j As Long
With ThisWorkbook.Worksheets("Dum")
For iColumn = 1 To 3
lLast = Application.Max(lLast, Cells(Rows.Count, iColumn) _
.End(xlUp).Row)
Next iColumn
j = 0
For i = 1 To 5
If lLast < 300 Then
MsgBox "Less than 300 rows", vbOKOnly
Exit Sub
Else
'MsgBox j
.Range("A" & lLast - 299 & ":C" & lLast).Cut Destination:= _
ThisWorkbook.Worksheets("Mud").Range("A1") _
.End(xlUp).Offset(0, j)
End If
j = j + 3
Next
End With
End Sub

Thanks,
Howard