Create List from Multiple Worksheets

[Guest]

I need to create a list of all zip codes on one worksheet that are contained
on 150 individual worksheets. Each worksheet has zip codes and some text in
column C. I want to create a worksheet at the end of the workbook and run a
macro that will list all the zip codes contained on the 150 worksheets in one
row so I can compare all the zip codes to a master list in another worksheet.
The macro needs to start with sheet 1, look at all the values in Column C,
add the ones that have 5 digit zip codes, and ignore blanks and text, and go
through all worksheets until the last sheet which will contain the list.
Can I get some help?

 

[Guest]

Create a new worksheet that is named summary and use the code below. Column
c on the summary sheet should be formated as text, otherwise, leading zeroes
will be truncated.

Sub zip_code()
Dim Cell_Data As String

zipcode_count = 0
For Each MyWorksheet In Workbooks(ThisWorkbook.Name).Worksheets
If StrComp("Summary", MyWorksheet.Name) <> 0 Then
RowCount = 1

Do While StrComp(MyWorksheet.Range("C1"). _
Offset(rowOffset:=RowCount - 1, columnOffset:=0), "End") <> 0

Cell_Data =
MyWorksheet.Range("C1").Offset(rowOffset:=RowCount - 1, columnOffset:=0).Value
If Len(Cell_Data) = 5 Then

Found_Char = False

For char_count = 1 To 5
character = Mid(Cell_Data, char_count, 1)
If (character < "0") And (character > "9") Then
Found_Char = True
Exit For
End If
Next char_count

If Found_Char = False Then

Worksheets("Summary").Range("C1").Offset(rowOffset:=zipcode_count, _
columnOffset:=0) = Cell_Data
zipcode_count = zipcode_count + 1

End If

End If

RowCount = RowCount + 1


Loop
End If
Next MyWorksheet

End Sub

 

[Guest]

Almost there...
I fixed the line with Cell_Data =
MyWorksheet... into one line and ran the program.

Got an Error 1004 on line;
Do While StrComp(MyWorksheet.Range("C1").Offset(rowOffset:=RowCount - 1,
columnOffset:=0), "End") <> 0

The results I got were the zips from the first worksheet, but also got one
cell of text, and then the macro stopped.
Here is the results received.,...
73301
78613
78630
78641
78642
78645
78646
78717
78718
78720
78726
78729
78730
78731
78732
78750
78755
78756
78757
78759
78766
78799
Total
The stopped.

 

[Guest]

I forgot to tell you that I needed to know where the last row oif data was in
each worksheet. I added the word END to the last cell of column C. If there
is another indication of the last row of data let me know. Maybe another
column has data in every row that can be used

then replace
Do While StrComp(MyWorksheet.Range("C1").Offset(rowOffset:=RowCount - 1,
columnOffset:=0), "End") <> 0

with

Do While StrComp(MyWorksheet.Range("A1").Offset(rowOffset:=RowCount - 1,
columnOffset:=0), "") <> 0

this tests for no data in column A.

 

[Guest]

I got one word wrong in the program. we need to change AND to OR

If (character < "0") OR (character > "9") Then

 

[Guest]

I see...no, each worksheet has a different number of zip codes. Maybe we
could put something in the code that would write the word "END" after all the
data in column C on all the worksheets and use the code as is...although it
is also bringing the word TOTAL over as well. I tried the A1 and "", but
received no results. I put the word END in column C on 4 pages and it worked
great to the 5th page. I've got 150 sheets though...ugh!
We are so close though!! Thanks so much already!

 

[Guest]

Thank you so much! That did it!!
I used the following code to put the word "END" in cell C100 of each
worksheet. All works great now. Thanks again for your help!

David