| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create List from Multiple Worksheets
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create List from Multiple Worksheets
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 "David" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create List from Multiple Worksheets
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. "Joel" wrote: 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 "David" wrote: 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create List from Multiple Worksheets
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. "David" wrote: 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. "Joel" wrote: 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 "David" wrote: 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create List from Multiple Worksheets
I got one word wrong in the program. we need to change AND to OR
If (character < "0") OR (character "9") Then "David" wrote: 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. "Joel" wrote: 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 "David" wrote: 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create List from Multiple Worksheets
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! "Joel" wrote: 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. "David" wrote: 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. "Joel" wrote: 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 "David" wrote: 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create List from Multiple Worksheets
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 "Joel" wrote: I got one word wrong in the program. we need to change AND to OR If (character < "0") OR (character "9") Then "David" wrote: 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. "Joel" wrote: 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 "David" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create multiple worksheets from list | Excel Discussion (Misc queries) | |||
| create & name multiple worksheets | Excel Worksheet Functions | |||
| Need to create unique list from list of multiple entries | Excel Programming | |||
| Create list of worksheets | Excel Discussion (Misc queries) | |||
| Create Multiple Worksheets from One | Excel Programming | |||
Linear Mode
