Here is modified code. I assumed in this new code the DT35 was in column A
(not B), tnherefore I had to extract the DT35 from the rest of the column A
data.


Sub movedata1()

With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Sh1RowCount = 1
Sh2RowCount = 1
Do While Sh1RowCount <= LastRow
ColAData = .Range("A" & Sh1RowCount)
If InStr(ColAData, "Plant No:") 0 Then
'extract the number only
PlantNo = Trim(Mid(ColAData, InStr(ColAData, ":") + 1))
SMUEND = .Range("D" & (Sh1RowCount + 2))
NewDate = .Range("A" & (Sh1RowCount + 2))
StringData = PlantNo & ",," & SMUEND & _
"," & NewDate
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = StringData
Sh2RowCount = Sh2RowCount + 1
End With
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub

"Jim G" wrote:

I have a data set that I download to excel and I need to select specifica
data to import into another programme. The raw data looks like this.


Row Col A Col B Col C Col D Col E
Col F
9 Date Shift Type SMU Start SMU End SMU Total Error Gap
10
11
12 Type: 45D (DUMPER)
13
14
15 Plant No: DT35
16
17 19/11/2007 DAY 4,904.00 4,977.00 73.00 0.00

This repeats for each plant number with the same row spacing.

I would like to use a macro to create a unique list for each plant item
("DT35") followed by two commas (,,) the SMU End (Col D unformatted) comma
and the date (DD/MM/YYYY). EG "DT35,,4977,19/11/2007 all in column A of a
new sheet wthout headings.

The lists will vary in length with each site.
--
Jim