Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selectively copying ranges from one sheet to second sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selectively copying ranges from one sheet to second sheet
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Copying the repeated data of the previous sheet to the next sheet | Excel Discussion (Misc queries) | |||
help with copying multiple ranges and paste it on a work sheet based on conditions | Excel Discussion (Misc queries) | |||
Dynamic column chart - copying from Sheet to Sheet. | Excel Discussion (Misc queries) | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |