Dave,

Had just wanted to thank you again for the program to pull out data from
a dataset based on color.

I also tried out your initial suggestion for doing this which was
(attached below). But somehow in creating it, there is always an error
which comes up at the line:
= Array("Sheet", "address", "value", "formula")
The whole line becomes red, and the error message is something referring
to "end of statement" or something like that.

Do you know what the problem might be and how to fix it?

Thanks.

Dave Shapiro

your original suggestion:

"Maybe something like this that loops through each cell in the usedrange
of each worksheet:"

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim SumWks As Worksheet
Dim myCell As Range
Dim oRow As Long

Set SumWks = Worksheets.Add
SumWks.Range("a1").Resize(1, 4).Value _
= Array("Sheet", "address", "value", "formula")

oRow = 1
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = SumWks.Name Then
'do nothing
Else
With SumWks
For Each myCell In wks.UsedRange.Cells
If myCell.Interior.ColorIndex = 3 Then
oRow = oRow + 1
.Cells(oRow, "A").Value = "'" & wks.Name
.Cells(oRow, "B").Value = myCell.Address(0, 0)
.Cells(oRow, "C").Value = "'" & myCell.Value
.Cells(oRow, "D").Value = "'" & myCell.Formula
End If
Next myCell
End With
End If
Next wks
End Sub




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!