Getting information from a different worksheet

[excelnewbie166]

Many many thanks to Chip Pearson for his help on Nov
28...but as usual developers don't tell users much about
the application. Here is what is happening -

I use a workbook with 8 worksheets. This workbook is used
by many diferent departments so I cannot modify it. The
first worksheet (Summary)is populated with information
such as store number, store opening date, store owner,
etc. There is another worksheet (Addresses) that has all
of the address information. Thanks to Chip, I have the
code that will query the Summary worksheet and give me a
list of all stores opening between two dates. Now I need
to query the Addresses worksheet in order to get the
address information. There is a common "key" (store
number) in all of the worksheets. Is there a way I can
grab this "key" and use it to query the address worksheet
so I can grab the address information?

Any and all help is greatly appreciated.

 

[Excelnewbie166]

Here is the code Chip has prepared:

Dim DestRng As Range
Dim SummaryRng As Range
Dim Startdate As Date
Dim EndDate As Date
Dim Rng As Range
Startdate = CDate(Me.Startdate.Text)
EndDate = CDate(Me.EndDate.Text)
Set DestRng = Worksheets("MoveHere").Range("A1")
Set SummaryRng = Worksheets("Summary").UsedRange.Columns(3)
For Each Rng In SummaryRng.Cells
If IsDate(Rng) Then
If Rng.Value >= Startdate And Rng.Value <= EndDate
Then
Rng.EntireRow.Copy Destination:=DestRng
Set DestRng = DestRng(2, 1)
End If
End If
Next Rng
Unload Me
End Sub

Sorry for not including it in the original post.

 

[Excelnewbie166]

Sorry Chip the comment about the developers wasn't pointed
at you, but at the developers in my own company. They have
set up this workbook and have lots of hidden items, etc
that they haven't pointed out to us in the field until we
start questioning them about the behaviour of certain
items.