Excel VBA - Using ComboBox value to copy data of multiple cells to an other sheet

0
votes

Sheet1 = Contains Fill in Data Sheet2 = Contains All the information

I am using Sheet 2 Column A2:A81 as Combobox1 Items, I need when an item is selected, the following cells of the same row will be copied to sheet1 eg;

Sheet 2:

Shop1 Germany 11111111

Shop2 Italy 2222222

Shop3 UK 33333333

Shop4 Russia 44444444

Combobox Item Selected: [Shop4]

Data from Sheet 2 is copied to sheet1

Sheet 1:

Shop4 Russia 44444444

anyone know how can I execute this ?

3
vbaexcelcombobox
Hi and welcome to the forum. Start by posting whatever code you've got. And please read this: stackoverflow.com/help/how-to-ask – Miqi180
Ryan, keywords that might help you on your search for how to do this include "worksheet cell on change event" and "copying cells with vba". – Cody Ｇ

3 Answers

0
votes

This includes the code that triggers the the combo box, You could do something like this,

Private Sub ComboBox1_Change()
   Sheets("Sheet1").Cells(2, 5).Resize(, 5).Value = _
   Sheets("Sheet2").Cells(ComboBox1.ListIndex + 2, 1).Resize(, 5).Value
End Sub

However, I do not know where on Sheet1 you want the data, so you would have to amend that range to suit your needs.

0
votes

Your Sheet1 sounds like a form, i.e. reusable template. In this case, using Data Validation on the 'Selection' cell and VLOOKUP() on the 'Copied Data' cells will suffice, negating the need for any controls or VBA.

0
votes

Ok, Managed to solve it:

Me.ComboBox1.List = Worksheets("Sheet2").Range("A2:A81").Value

        Worksheets("Sheet2").Cells(ComboBox1.ListIndex + 2, 1).Copy Worksheets("Sheet1").Cells(lastrow, 1)
        Worksheets("Sheet2").Cells(ComboBox1.ListIndex + 2, 2).Copy Worksheets("Sheet1").Cells(lastrow, 2)
        Worksheets("Sheet2").Cells(ComboBox1.ListIndex + 2, 3).Copy Worksheets("Sheet1").Cells(lastrow, 3)
        Worksheets("Sheet2").Cells(ComboBox1.ListIndex + 2, 4).Copy Worksheets("Sheet1").Cells(lastrow, 4)
        Worksheets("Sheet2").Cells(ComboBox1.ListIndex + 2, 5).Copy Worksheets("Sheet1").Cells(lastrow, 5)

Thanks :)