Merge Info in 76 worksheet tabs in Excel 2

[Queryman]

I have a worksheet with 76 tabs of zip code information. Some of the tabs have the zips as text, some as numbersm, some tabs have just a cell popoulated with info saying that no census is available. Is there any shortcuts that anyone could suggest that I could use to make one worksheet that contains the information from all 76 sheets with only zips and somehow drop all the stuff like "no census is available" and also have the zips all in the saqme format. Much obliged. If this helps that info I want to keep is always five characters long (zip code)

Thanks

Michael

 

[dawnd3]

Is the data all in one column? Is it in the same column in each sheet?

Dawn

http://www.calliopeconsulting.net

 

[DaleWatson123321]

Michael,

I've created a working model based on the specifics you've described.

This model uses Excel's "database extraction" function to "filter" out and extract the required data to a separate sheet.

Because Excel's database functions are built-in custom functions, they are written in the "C" language and are therefore "lightning fast" in comparison to using strictly VBA.

If you'd prefer, I can email you the file. Just email me, and I'll send the file via return email.

If you want to set the file up on your own, here's the code... (Copy it into a Module)

Sub Combine_Data()
'combines zipcode data from all sheets into sheet
'named "AllData".

Application.ScreenUpdating = False
cnt = ThisWorkbook.Worksheets.Count - 3
n = 0

For n = 1 To cnt
Sheets.Select
Set_Data
Extract_Data
Set_ExtData
Copy_ExtData
Next n

Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub

Sub Set_Data()
'sets the input database range name ("data&quot
Application.Goto Reference:="R1C1"
Selection.Insert Shift:=xlDown
ActiveCell.Value = "Z_C"
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(1, 0).Address
rng = FirstCell & ":" & LastCell
Range(rng).Name = "data"
Range("data&quot.NumberFormat = "@"
Application.Goto Reference:="R1C1"
End Sub

Sub Extract_Data()
'extracts zip numbers to Extraction sheet
Application.ScreenUpdating = False
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("ext&quot, _
Unique:=False
End Sub

Sub Set_ExtData()
'sets the range name "extdata" for extracted data
Worksheets("Extraction&quot.Select
Application.Goto Reference:="R2C1"
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(1, 0).Address
rng = FirstCell & ":" & LastCell
Range(rng).Name = "extdata"
End Sub

Sub Copy_ExtData()
'copies range named "extdata" to "AllData" sheet
Worksheets("AllData&quot.Select
LastCell = [A65536].End(xlUp).Offset(1, 0).Address
Range(LastCell).Select
Range("extdata&quot.Copy
ActiveSheet.Paste
End Sub

=============
IMPORTANT:
=============

Before running the routine "Combine_Data", you'll need to perform the following steps...

1) Insert 3 sheets to the right of your 76 sheets.

2) Name these sheets: Extraction, AllData, and Criteria.

3) In the Extraction sheet, enter "Z_C" in cell A1.

4) Assign the range name "ext" to cell A1.

5) In the AllData sheet, enter "Z_C" in cell A1.

6) In the Criteria sheet, enter the label "crit" in cell A4.

7) In cell B5, enter this formula... =LEN(z_c)=5

8) Highlight B4:B5, and assign the range name "crit".

9) On the Criteria sheet, add a macro button, name it "Combine Data", and attach the routine "Combine_Data".

That's all. Clicking the button will of course merge your data.

Note: While there are different ways to create range names, the method I always recommend is...
a) Highlight the cell or range-of-cells
b) Hold down <Control> and hit <F3>
c) Type the name
d) Hit <Enter>

I hope this works to your satisfaction. Please advise as to how you make out. Don't hesitate to ask for the file if you prefer.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca