Excel Macro for copy and paste according to comboBox item

Question

I have a comboboxin excel, based on the item selected in it i want to copy data from one sheet to another. Below is a part of the code when I run it i get "Run time error 1004 " I am a started in VBA

Private Sub ComboBox1_Change()

     Dim firstLimit As Integer
     Dim secondLimit As Integer
     firstLimit = 2
     secondLimit = 2

     Application.ScreenUpdating = False
     Worksheets("output").Range("A2:U2").Value =   Worksheets("Input").Range(Cells(firstLimit, "A"), Cells(secondLimit, "U")).Value
     Application.ScreenUpdating = True


     End Sub

Thanks

score 1 · Accepted Answer · answered Mar 06 '12 at 05:20

The error is caused by the unqualified calls to Cells. In your code these will refer to the active sheet. If this is not "Input", then an error occurs.

Change to

Sub ComboBox1_Change()
    Dim firstLimit As Integer
    Dim secondLimit As Integer
    firstLimit = 2
    secondLimit = 2

    Application.ScreenUpdating = False
    With Worksheets("Input")
        Worksheets("output").Range("A2:U2").Value = .Range(.Cells(firstLimit, "A"), .Cells(secondLimit, "U")).Value
    End With
    Application.ScreenUpdating = True


End Sub

Note the.'s before Range and the two Cells

Excel Macro for copy and paste according to comboBox item

1 Answers1