Copying Named Ranges based on ListBox Results

[Guest]

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 form’s 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
--------------------------------------------------

 

[Guest]

Instead of copying and pasting from one spreadsheet to another you combine
and store your data in a database such as MS Access or MS SQL Express. Then
in Excel VBA you can execute an ADO query and pull the data that you need
into your sheet. Then consider that sheet as a snapshot frozen in time. You
will have your source data (your "master" if you will) stored only once and
whenever you need copies or subsets of data you simple execute queries that
can filter the data and copy it into your spreadsheet.

Also, keeping your data in a database has many other benefits. To begin
with you can normalize the data so that you don't have duplicate values and
you can also add columns and related data without having to do it in 5
separate places (the worksheets).

If you insist on using Excel only then you can also upgrade to Excel 2007
which increases the row limitation to 1,048,576 rows.

Personally, I would not copy and paste data from spreadsheet to spreadsheet.

I hope this helps.

- Al