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
    Private Sub ComboBox1_Change()
    Range("A8:K40").ClearContents
    Dim strName As String
    If Me.ComboBox1.Value > 0 Then
        strName = ComboBox1
        Range(strName).Copy _
                Destination:=Header.Range("A8").End(xlToLeft)(1, 2)
    End If
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
    Private Sub ComboBox1_Change()
    With Range("A8:K40")
        .ClearContents
        .Borders.LineStyle = xlNone
        .Interior.ColorIndex = xlNone
    End With
    Range(ComboBox1.Text).Copy Destination:=Range("A8").End(xlToLeft)(1, 2)
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.

    Love 1

    You are very welcome. :)

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login