Combobox to Copy Row Data

[ccsher77]

Hi Everyone,

I am new to VBA and new to this forum, so any help would be gratefully recieved.

I have a spread sheet that has the Months of the year in column A, then there are 4 values for each month in columns B,C,D,E.
Example:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> A B C D E</o>
Jan 100 97 98 99
<o> </o>
I have built a userform with a combobox which I have populated with the list of months in column A by using the RowSource field in the combobox property window.
<o> </o>
What I am trying to do is to copy all of the data of the relevant row which is selected by the month in the combobox.
<o> </o>
i.e.
The user opens the userform and from the combo box selects Jan (as example above)
I then want to be able to copy all of the data on the “Jan” Row and paste it into a separate sheet.
<o> </o>
I can achieve the copy and paste section by using macro recorder but I am struggling to work out how to select the entire row from the combobox selection.
<o> </o>
Can anyone help?

Thanks in advance

Craig

 

[Sthrncali]

Hi Everyone,

I am new to VBA and new to this forum, so any help would be gratefully recieved.

I have a spread sheet that has the Months of the year in column A, then there are 4 values for each month in columns B,C,D,E.
Example:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>A B C D E</o>
Jan 100 97 98 99
<o></o>
I have built a userform with a combobox which I have populated with the list of months in column A by using the RowSource field in the combobox property window.
<o></o>
What I am trying to do is to copy all of the data of the relevant row which is selected by the month in the combobox.
<o></o>
i.e.
The user opens the userform and from the combo box selects Jan (as example above)
I then want to be able to copy all of the data on the “Jan” Row and paste it into a separate sheet.
<o></o>
I can achieve the copy and paste section by using macro recorder but I am struggling to work out how to select the entire row from the combobox selection.
<o></o>
Can anyone help?

Thanks in advance

Craig

Try this...

Dim myMonth as String
Dim ws1 as Worksheet
Dim ws2 as Worksheet


Set ws1 = Sheet1 'Change this to your source tab
Set ws2 = Sheet2 'Change this to your destination tab

myMonth = cboBox1.Value 'Change this to your ComboBox name

ws2.Range("A1").Resize(1,5).value = Application.WorksheetFunction.Vlookup(myMonth, ws1.Range("A:E"), 1, False).Resize(1,5).value

'Change ".Resize(1,5)" to the number of columns you want to copy. I.E. 1,5 will copy range "A:E" to the new sheet. If you wanted "A:F" for example, then change to (1, 6).

'This will bring the whole chunk of data, including the month into your new sheet. If you only want the results, not the month, then change "ws1.Range("A:E"), 1" to "ws1.Range("A:E"), 2" and shorten the ".Resize(1,5)" to the apporpriate number of columns.

 

[Sthrncali]

Should have tested it first... Here is a modified version that worked for me:

Sub CopyRow()
Dim myMonth As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim FoundRow As Integer

Set ws1 = Sheet25 'Change this to your source tab
Set ws2 = Sheet26 'Change this to your destination tab
myMonth = cboBox1.Value 'Change this to your ComboBox name
For c = 1 To 5
ws2.Cells(1, c).Value = Application.WorksheetFunction.VLookup(myMonth, ws1.Range("A:Z"), c, False)
Next
'Change "For c = 1 To 5" to however many columns of data you want to return. I.E.
'if you need to return 10 columns of data, then change it to C = 1 to 10."
End Sub

 

[ccsher77]

Hi Thanks for you reply.

I have tried inputting your code above as a Private Sub CommandButton operation.
Howver I get the following error:
Run Time Error 424
Object required"

As I said I am very new to VBA so I have no idea how to rectify this, would you be able to help?

 

[Sthrncali]

Hi Thanks for you reply.

I have tried inputting your code above as a Private Sub CommandButton operation.
Howver I get the following error:
Run Time Error 424
Object required"

As I said I am very new to VBA so I have no idea how to rectify this, would you be able to help?

You need to edit the form your combobox is located on, and add a "Submit" button. Then double click the submit button and paste the code into that window.