Sorting Data

[christie.taru]

I have one column with Dates and several columns with Instrument
readings, eg. Inst1, Inst2, Inst3, etc.The instruments are not read
everyday so some of the dates have blank data or zero value in the
Instruments columns. How can I write a macro to filter out the dates
with a non-zero or non-blank Instrument reading and populate in a
different sheet for each Instrument?

 

[Joel]

The code below is close to being right. I don't know where the instrument
names are located from your posting. I think this line in the code will need
to be changed

SHName = Cells(RowCount, _
ColumnCount).Value

right now the code is using the reading value as the sheet names. The
results will look strange.


Sub copyinstruments()

Const DateColumn = "A"
Const InstrumentStartCol = "B"
Const InstrumentEndCol = "F"

StartCol = Cells(1, InstrumentStartCol).Column
EndCol = Cells(1, InstrumentEndCol).Column

LastRow = Cells(Rows.Count, DateColumn). _
End(xlUp).Row

Set FirstSheet = ActiveSheet

For RowCount = 1 To LastRow
For ColumnCount = StartCol To EndCol
If Not IsEmpty(Cells(RowCount, _
ColumnCount)) Then

SHName = Cells(RowCount, _
ColumnCount).Value
'check if worksheet exists
Found = False
For Each ws In Worksheets
If ws.Name = SHName Then
Found = True
Exit For
End If
Next ws

If Found = False Then
Worksheets.Add after:= _
Worksheets(Worksheets.Count)
ActiveSheet.Name = SHName
FirstSheet.Activate
End If

With Worksheets(SHName)
If Found = True Then
LastRow = .Cells(Rows.Count, _
"A").End(xlUp).Row
NewRow = LastRow + 1
Else
NewRow = 1
End If

.Cells(NewRow, "A") = _
Cells(RowCount, DateColumn).Value
.Cells(NewRow, "B") = _
Cells(RowCount, ColumnCount).Value
End With
End If
Next ColumnCount
Next RowCount

End Sub