Excel - Copy range of cells based on combo box selection 1

[Excelerate2004]

I'm just wondering is it possible that based on a user selection in a dropdown combo box, that a range of cells is copied from sheet1 and move them to another sheet2

Thanks for any help I can get as i'm lost as to what to do next??

 

[SkipVought]

Hi,

You need BOTH a Source Range to copy from and a Destination Range to 'paste to'.

You only mentioned a combobox with, I assume, EITHER a copy from range or a paste to range.

What/where is the copy from range?

What/where is the past to range?

If it's simply copy from the combobox selection to A1 on Sheet 2 then

sheet1.range(combobox1.value).copy destination:=sheet2.[A1]

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Excelerate2004]

Sorry, ok I guess I should elaborate a bit further:

I have a combo box that looks up a list with 29 objects through data validation.

I would like the user to be able to select a specific object from that list and automatically copy whats in the cells on a sheet called 'dscombo'!C11:C20:G19 and paste the values from those cells into a sheet called 'Step 2'!C11:C20:G19.

The key point that I should make is that the range of cells to be copied and pasted will vary depending on what object is selected from my combo box.

In the range described above that I want copied and pasted, the user would have selected the first object from the list in my combo box.

I hope this helps explain my question.

Thanks

 

[Excelerate2004]

Further elaboration:

The combo box is filled from a sheet called 'Species list' with a range A2:A30. The combo box is located on a sheet called Step 2 in cell C5.

 

[SkipVought]

First: Minor point -- you are not selecting OBJECTS. In Excel, especially in Visual BASIC for Applications, Object means something very different.

Second: There is no way to COPY 'n' PASTE using worksheet functions. Furthermore, the Data/Validation feature produces no worksheet event that can be trapped, such as the Worksheet_Change event, which detects CHANGES in VALUES that are ENTERED in a worksheet. Changes in values by virtue of a FORMUAL is NOT a change that Excel can detect.

BOTTOM LINE: Using the Data/Validation feature cannot automatically trigger a COPY 'n' PASTE operation.

You could...

1) add a button to do the COPY 'n' PASTE operation.

2) use a Control Toolbox Combobox or Forms Combobox instead of the Data/Validation feature. The Click event can run a COPY 'n' PASTE operation.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Excelerate2004]

I think I understand what you have described.

One question:

If I replace my Data/Validation feature with an actual Control Toolbox combobox, will I be able to create a click event that copies and pastes a range from source to destination based on what is selected in that combo box? i.e. the ranges copied and pasted will vary based on the combo box selections using the click event.


Or as you have stated:

"There is no way to COPY 'n' PASTE using worksheet functions."

Thanks for your help.

 

[SkipVought]

[tt]
'dscombo'!C11:C20:G19
[/tt]
is identical to
[tt]
'dscombo'!C11:G20
[/tt]
Is that what you want?

To copy and paste from the combobox click event

Private Combobox1_Click()
  With Combobox1
    sheets("dscombo").range(.value).Copy _
    Destination:=sheets("Step 2").range(.value)
  End with
End Sub

The code can be pasted into the Sheet object containing the combobox -- right click the sheet tab and select View Code

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Excelerate2004]

Having trouble with writing the code, getting back errors from within the VBA program.

Is this correct?

Private Sub ComboBox1_Click()
With ComboBox1
Sheets("dscombo").C11:G20.Burbot.Copy _
Destination:=Sheets("Step 2").C11:G20.Burbot
End With
End Sub

Thanks for all your help.

 

[Excelerate2004]

I was able to get the following code to work

Private Sub ComboBox1_Click()
With ComboBox1
Sheets("dscombo").Range("C11:C20").Copy _
Destination:=Sheets("Step 2").Range("C11:C20")
End With
End Sub

However, I'd like to incorporate this by having a unique selection in the combo box copy & paste a different range each time, For example:

There are 29 items/choices in my combo box.

So for each selection up until selection 29 I'd like to be able to do the following:

If comboboxselection = 1 Then
Copy from 'dscombo'!C11:G20 & paste to 'Step 2'!C11:C20

If comboboxselection = 2 Then
Copy from 'dscombo'!C49:G58 & paste to 'Step 2'!C49:C58

If comboboxselection = 3 Then
Copy from 'dscombo'!C87:C96 & paste to 'Step 2'!C87:C96
.
. etc. up to 29

Is this possible?

 

[SkipVought]

Private Sub ComboBox1_Click()
  Dim rng as range
    With ComboBox1
      Select Case .Value
        Case 1:
          Set rng = range("C11:G20")
      End Select
    End With
    Sheets("dscombo").rng.Copy _
    Destination:=Sheets("Step 2").rng
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Excelerate2004]

Thanks, Thats exactly what I was looking for!

Great advice, much appreciated!