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
"VBA_Newbie79" wrote:
> 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
> --------------------------------------------------
|
Similar Threads
