COPY AND PASTE A RANGE BASED ON VALUE OF A COMBO BOX

  • Good day,


    would greatly appreciate your help to resolve the following issue:


    - Have a combobox that can pickup values from a list (not a table). All values available are names of specific ranges in the same sheet (named "Header")

    - By changing the value on the combobox, i need the range named after the combobox value to be pasted to the cell A8 of the same sheet.

    - Before each time a new range is pasted, the area A8:K40 should be cleared


    I wrote the following code by using some examples from other threads in the forum, but get error message "run-time error '424':Object required"..

    Any assistance would be more than welcomed!!


    Code
    1. Private Sub ComboBox1_Change()
    2. Range("A8:K40").ClearContents
    3. Dim strName As String
    4. If Me.ComboBox1.Value > 0 Then
    5. strName = ComboBox1
    6. Range(strName).Copy _
    7. Destination:=Header.Range("A8").End(xlToLeft)(1, 2)
    8. End If
    9. End Sub
  • It would be easier to help if you could attach a copy of your file (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You have merged cells in the named ranges. You should avoid using merged cells as they almost always create problems for macros. Start by unmerging all the merged cells in your named ranges and then try this macro:

    Code
    1. Private Sub ComboBox1_Change()
    2. With Range("A8:K40")
    3. .ClearContents
    4. .Borders.LineStyle = xlNone
    5. .Interior.ColorIndex = xlNone
    6. End With
    7. Range(ComboBox1.Text).Copy Destination:=Range("A8").End(xlToLeft)(1, 2)
    8. End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hello Mumps. Many thanks for your assistance. It works perfectly and worked even with one range that did have two cells merged :)


    Have a nice day and thank you once again! :thumbup::thumbup::thumbup:


    Best regards,

    ThanPanag