selectively copying ranges from one sheet to second sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First time user

I have a sheet with multiple range names (range1, range2, range3, etc). I would like to have a list box with the range names in that list. If the user selects range2, then that range is the one that I would like to copy to a predetermined range on sheet 2. The range sizes are the same. Also, if they make a mistake, and select the wrong range, I would like for them to be able to reselect the appropriate range. I can get this to work with a macro where I enter the "from" range. I would like to automate that with the list box or something like that. Anything with the names already predefined so user can't enter an incorrect range name.
 
JPTIII

First you need a sub that fills the listbox, like this

Sub PopLb()

Dim nm As Name

For Each nm In Sheet1.Parent.Names
Sheet1.ListBox1.AddItem nm.Name
Next nm

End Sub

Then you need to use the Click event of the listbox, like this

Private Sub ListBox1_Click()

Me.Range(Me.ListBox1.Value).Copy Sheet2.Range("a1")

End Sub

To get to the click event, select the listbox and choose View Code from the
right click menu. Use the dropdowns to select the Click event.

For the first sub, you have to decide when you want to run. For instance,
you can run it every time the workbook opens by using the Workbook_Open
event. Or if the range names won't ever change, you can just run it once
and be done with it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

JPTIII said:
First time user.

I have a sheet with multiple range names (range1, range2, range3, etc). I
Click to expand...
would like to have a list box with the range names in that list. If the
user selects range2, then that range is the one that I would like to copy to
a predetermined range on sheet 2. The range sizes are the same. Also, if
they make a mistake, and select the wrong range, I would like for them to be
able to reselect the appropriate range. I can get this to work with a macro
where I enter the "from" range. I would like to automate that with the list
box or something like that. Anything with the names already predefined so
user can't enter an incorrect range name.
 
Back
Top