Hi all,

I'm guessing that I'm posting at the wrong time of day because I haven't received any responses to my last two posts, but I am at a dead end.

I have over 200 worksheets in a workbook. Each one of these worksheets has the exact same format, with similar types of data in each cell. As an example, in each worksheet some of the data looks like this

C2 C3 C4
status number name

Status is one of three things: complete, in progress, or major issue. The number is a tracking number such as 1.1 or 5.6.3. The name is the name of the project that is being tracked, so something like Enterprise Support.

What I have to figure out how to do is create a list that shows all of the numbers and names associated with a status. So for 'complete' I need to find a way to return all of the tracking numbers and names for projects that are complete. This would be very simple to do with an auto filter if everything was in one sheet, but each project has it's own worksheet. Any help or a push in the right direction would be greatly appreciated.

Thanks

The only solution I have is to use a macro. Right-Click the sheet tab
for the sheet you wish to generate the list in and paste this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rws As Worksheet
If Not Target.Address = Range("A1").Address Then Exit Sub
rws.Columns("B:D").ClearContents
For Each ws In ActiveWorkbook.Sheets
If ws.Name < rws.Name Then
If ws.Range("C2") = Target Then
ws.Range("C2:C4").Copy
If rws.Range("B1") = "" Then
rws.Range("B1").PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Else
rsw.Range("B" & rws.Rows.Count).End(xlUp).Offset(1,
0) _
.PasteSpecial Paste:=xlPasteAll,
Transpose:=True
End If
End If
End If
Next
End Sub

Enter your status code in Cell A1 and Column B-D should hold the
resulting data

HTH

Charles Chickering

xlsuser42 wrote:
Hi all,

I'm guessing that I'm posting at the wrong time of day because I
haven't received any responses to my last two posts, but I am at a dead
end.

I have over 200 worksheets in a workbook. Each one of these worksheets
has the exact same format, with similar types of data in each cell. As
an example, in each worksheet some of the data looks like this

C2 C3 C4
status number name

Status is one of three things: complete, in progress, or major issue.
The number is a tracking number such as 1.1 or 5.6.3. The name is the
name of the project that is being tracked, so something like Enterprise
Support.

What I have to figure out how to do is create a list that shows all of
the numbers and names associated with a status. So for 'complete' I
need to find a way to return all of the tracking numbers and names for
projects that are complete. This would be very simple to do with an
auto filter if everything was in one sheet, but each project has it's
own worksheet. Any help or a push in the right direction would be
greatly appreciated.

Thanks




--
xlsuser42

I had a chance to debug my code and found some issues. Use this code
instead:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rws As Worksheet
Set rws = ActiveSheet
If Not Target.Address = Range("A1").Address Then Exit Sub
rws.Range("B1:D1").EntireColumn.ClearContents
For Each ws In ActiveWorkbook.Sheets
If ws.Name < rws.Name Then
If ws.Range("C2") = Target Then
ws.Range("C2:C4").Copy
If rws.Range("B1") = "" Then
rws.Range("B1").PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Else
rws.Range("B" & rws.Rows.Count).End(xlUp).Offset(1,
0) _
.PasteSpecial Paste:=xlPasteAll,
Transpose:=True
End If
End If
End If
Next
End Sub

All other instructions should stay the same.

Charles

Die_Another_Day wrote:
The only solution I have is to use a macro. Right-Click the sheet tab
for the sheet you wish to generate the list in and paste this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rws As Worksheet
If Not Target.Address = Range("A1").Address Then Exit Sub
rws.Columns("B:D").ClearContents
For Each ws In ActiveWorkbook.Sheets
If ws.Name < rws.Name Then
If ws.Range("C2") = Target Then
ws.Range("C2:C4").Copy
If rws.Range("B1") = "" Then
rws.Range("B1").PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Else
rsw.Range("B" & rws.Rows.Count).End(xlUp).Offset(1,
0) _
.PasteSpecial Paste:=xlPasteAll,
Transpose:=True
End If
End If
End If
Next
End Sub

Enter your status code in Cell A1 and Column B-D should hold the
resulting data

HTH

Charles Chickering

xlsuser42 wrote:
Hi all,

I'm guessing that I'm posting at the wrong time of day because I
haven't received any responses to my last two posts, but I am at a dead
end.

I have over 200 worksheets in a workbook. Each one of these worksheets
has the exact same format, with similar types of data in each cell. As
an example, in each worksheet some of the data looks like this

C2 C3 C4
status number name

Status is one of three things: complete, in progress, or major issue.
The number is a tracking number such as 1.1 or 5.6.3. The name is the
name of the project that is being tracked, so something like Enterprise
Support.

What I have to figure out how to do is create a list that shows all of
the numbers and names associated with a status. So for 'complete' I
need to find a way to return all of the tracking numbers and names for
projects that are complete. This would be very simple to do with an
auto filter if everything was in one sheet, but each project has it's
own worksheet. Any help or a push in the right direction would be
greatly appreciated.

Thanks




--
xlsuser42