Hello everyone,
All of you have been wonderful in my search for VBA understanding. This is
the first time I have a question for you.

I have 5 separate worksheets that contain various regions of data.
Eventually they will exceed Excel's row limit if they are combined, which is
why they are separated to begin with. Each worksheet has the same columns,
but the data is specific to that region. I have also created dynamic named
ranges for each worksheet, since their size will increase every month. Using
a userform, I need to be able choose one or more regions from a list of
regions (which corresponds to the sheet names and the named ranges), then
copy and paste the data from each chosen worksheet into the €œMaster€�
worksheet. Since the column headings are all the same, the €œMaster€�
worksheet already has them listed.

For example, I need the userform to allow me to choose Northeast, Midwest,
and Southeast from the list below. When I click a button, the data in
Northeast (by named range?) would be pasted into the €œMaster€� worksheet,
starting in the second row. Then the data in Midwest would be pasted below
the Northeast data, and then the data in Southeast would be pasted below the
Midwest data.

<Regions
Northeast
Southeast
Midwest
Southwest
West

Below is the coding I have so far. The forms name is Change_Region, and
the regions are listed in a named range called Regions. Any help you can
provide would be very much appreciated. Thank you.

--------------------------------------------------
Private Sub CommandButton2_Click()

Dim res As Variant
Dim Last As Integer

For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) Then
res = Application.VLookup(Change_Region.ListBox1.Text = i,
Worksheets("LISTS").Range("Regions"), 1, False)
If Not IsError(res) Then
Last = Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
Change_Region.Hide
Application.ScreenUpdating = False
Worksheets(res).Select
Worksheets(res).Range(i).Copy Worksheets("Master").Range(Last +
1, 1)
Else
Change_Region.Hide
MsgBox "Match not made. Please try again."
Change_Region.Show
End If
Else
Change_Region.ListBox1.Clear
MsgBox "Please choose a region or click Cancel."
End If
Next i

End Sub
--------------------------------------------------