Okay. Try this:
Sub rjnc()
Dim i As Long, ws As Worksheet, ws2 As Worksheet, y As Long, x As Long, z As Long
Set ws = Workbooks("Report20Sept15.xlsx").Sheets("Extract")
Set ws2 = Workbooks("Master20File.xlsx").Sheets("Data")
With ws
For i = 11 To .Range("F" & Rows.Count).End(3).row Step 6
.Cells(i, "D").Copy .Cells(i + 1, "E").Resize(4)
.Range(.Cells(i + 1, "D"), .Cells(i + 5, "P")).Copy ws2.Range("B" & Rows.Count).End(3)(2)
Next i
y = ws2.Range("B" & Rows.Count).End(3).row
x = ws2.Range("A" & Rows.Count).End(3).row
With ws2.Range("A" & Rows.Count).End(3)(2)
.Formula = "=MAX(" & Range(ws2.Cells(2, "A"), ws2.Cells(x, "A")).Address(False, False) & ")"
.Value = .Value
End With
x = ws2.Range("A" & Rows.Count).End(3).row
z = ws2.Range("A" & Rows.Count).End(3)(0).Value + 1
ws2.Range(ws2.Cells(x, "A"), ws2.Cells(y, "A")).Value = z
End With
End Sub
Bookmarks