I have a few questions

It look like you are sortine the destinations by name and number (1a, 2a,
3a...) where is the name a number coming from?

There si a website to get zipcodes. I wrote a macro last year for somebody
to get the names of the city from the zip code using this website. I can do
something similar if it helps.

Sub GetZipCodes()

ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Form(0).Submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
If Table(0).Rows(0).innertext = "" Then
MsgBox ("Invalid Zip code")
Else
Location = Table(0).Rows(2).innertext
End If
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub





"VickiV" wrote:

Sure.

The Source is the sample provided originally. It is a table of all states
and their acceptable zips, plus some other columns of data I do not need to
bring into the destination worksheet. It has a named range of Territory that
identifies the table range. Column A lists the State field, Columns B & C
have the Zips field. Their column headings are identical to the corresponding
column headings in the destination field (the data is just in a different
columnar order).

The destination will only have data for one State at a time. I have a
worksheet for each state, and I want to filter the Source for the matching
state of the desitination worksheet. For instance, FL has 3 valid rows of
data in Source. I want to copy the FL data for columns B & C to the
corresponding columns in the destination (D & E). Then last but not least,
in the destination worksheet, copy this range of values down to the end of
the destination table. So for FL destination worksheet, it would look like
this (FL has 3 valid rows of zips):

For instance, FL destination will look like this (simplified...alot of other
columns in reality):
Columns:
A B C D E
FL 1a NameA 33301 33999
FL 2a NameA 34401 34999
FL 3a NameA 35501 39999
FL 1b NameB 33301 33999
FL 2b NameB 34401 34999
FL 3b NameB 35501 39999
FL 1c NameC 33301 33999
FL 2c NameC 34401 34999
FL 3c NameC 35501 39999

The other states would have the same repeating pattern for the range of
valid zips for them. For instance: CA has only 2 valid zips. It would copy
the 2 valid rows/columns of zip data to the appropriate column (D & E in
destination example), then repeat the patten down to the end of the table.

For instance, CA destination will look like (simplified...alot of other
columns in reality):
Columns:
A B C D E
CA 1a NameA 47701 47999
CA 2a NameA 48222 48999
CA 1b NameB 47701 47999
CA 2b NameB 48222 48999
CA 1c NameC 47701 47999
CA 2c NameC 48222 48999
CA 1d NameD 47701 47999
CA 2d NameD 48222 48999

I hope that is more clear. Thanks for your consideration!

"Joel" wrote:

Can you give examples of the data in both workbooks. I'm not sure from yor
example if the table you provided is the source or the destination data.

"Jill Smith" wrote:

Hi-
I really need some input on this one. I have a lookup table that identifies
valid zipcodes for states/provinces. What I need is to create a macro that
helps poplulate a number of worksheets with the valid zipcodes for the
identified state.

For instance: My Lookup Table (in a separate file) has the valid ZipLow and
ZipHighs for each state (see sample below). I have a macro that lookups the
state and inserts the number of rows necessary for each state (as they vary
per state) which gives me a good table to work with. Then I would like to
populate the ZipHigh and ZipLow columns based on the State column value until
the table is filled with appropriate zips for each state in my table. I
would imagine the easiest way to do this would be to see the value in the
state column, then review the lookup table to copy in the range that is equal
to the state value, then repeat for each new state found. I am confused
about how to copy in the dynamic range from the lookup table, which would
vary depending on the state.

Sample lookup table:

State ZipLow ZipHigh
FL 33301 33999
FL 34401 34999
FL 35501 35999
CA 47701 47999
CA 48222 48999
MI 60001 60999

Thank you so much,
Jill