If Application.WorksheetFunction.CountIf(.Cells(myR, 2).Resize(, myCol - 1),
mySht) 1
Then

there is a syntax error somewhere on this line....

"Bernie Deitrick" wrote:

Allan,

Try this version. I will try to answer your questions in another message, in reply to that post.

HTH,
Bernie
MS Excel MVP

Sub TryNow2()
'Column A - Date and hour (data comes in every hour)
'Column 's B-F Hard or Soft (rows could be all hard, or all soft, or any
'combination of the 2 words)
'Columns G-AL Data points referring to columns B-F. ie
'Link column B(row) to range G(row)-M(row)
'link column C(row) to range N(row)-R(row)
'link column D(row) to range S(row)-X(row)
'link column E(row) to range Y(row)-AD(row)
'link column F(row) to range AE(row)-AL(row)

Dim myR As Long
Dim myRow As Long
Dim myCol As Integer
Dim mySht As String
Dim myCols As String

With Worksheets("Data Sheet")
For myR = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
For myCol = 2 To 6
mySht = .Cells(myR, myCol).Value
If mySht = "" Then GoTo BlankCell
If Application.WorksheetFunction.CountIf(.Cells(myR, 2).Resize(, myCol - 1), mySht) 1
Then
myRow = Sheets(mySht).Cells(Rows.Count, 1).End(xlUp).Row
Else
myRow = Sheets(mySht).Cells(Rows.Count, 1).End(xlUp)(2).Row
End If
myCols = Application.WorksheetFunction.Choose _
(myCol - 1, "G:M", "N:R", "S:X", "Y:AD", "AE:AL")
Sheets(mySht).Cells(myRow, 1).Value = .Cells(myR, 1).Value
Intersect(Sheets(mySht).Cells(myRow, 1).EntireRow, _
Sheets(mySht).Range(myCols).Offset(, -5)).Value = _
Intersect(.Cells(myR, 1).EntireRow, .Range(myCols)).Value
BlankCell:
Next myCol
Next myR
End With

End Sub






"afaust" wrote in message
...
Beautiful.... thank you very much... it works great, however, there is one
small problem. The data points are references to other objects, and having a
new line for each of the references moves the data out of those reference
points.... ie the data in columns G-M at 11:00 on 12/01/08 should be in
column G-M on sheet hard on the same line as the date/time and on the same
line as the hard data from 11:00 on 12/01/08 in columns n-r.
Just in case, to show it better;
Data was;

12/01/08 11:00 hard hard hard soft hard 1 2 3 4 5 6 7 8 9 10(whereby 1 and 2
would be reffed to the first hard 3 and for to 2nd for the purposes of
explanation) and the macro converts that to

12/01/08 11:00 1 2
12/01/08 11:00 3 4
etc
and it should be;
12/01/08 11:00 1 2 3 4 5 6 - - 9 10
on one sheet and
12/01/08 11:00 - - - - - - 7 8 - -

because once this is all said and done, I need to take daily averages of the
different data references...
I really appreciate the help with this, just what you've done to date has
been a major help.....

Allan

"Bernie Deitrick" wrote:

Allan,

Try the macro below. The assumption that I've made is that any value in columns B-F, row 2
onward
are valid sheet names (if the cell is not blank), and that your sheet with the data is named
"Data
Sheet"

HTH,
Bernie
MS Excel MVP