Add current row to another Worksheet According to combobox value

  • Thread starter Thread starter Memana
  • Start date Start date
M

Memana

Dear All,
I want to add currently selected row to another Worksheet depends
upon the value selected using a cmbobox in that Row.

For example, Suppose i have an xls file with 3 Worksheets. The name
of the 3 work sheets are "All" "New" & "Old".

All the rows in the "All" named worksheet contains a combobox
selection cell, and the values in the combo box are "New" & "Old". If
I select "New" value from the combo box, then that row should be added
to the "New" named Worksheet. If I select the "Old" value from the
combo box, then that row should be added to "Old" Worksheet. Please
tell me how can I do that.

Thanks in advance,
Mem.
 
This is one spot that I'd use the DropDown from the Forms toolbar instead of the
combobox from the Control toolbox toolbar.

Then I could assign the same code to each dropdown--instead of having to have
code for each combobox.

I put dropdowns in each row and made sure that each could only have OLD and NEW
as valid choices.

I had this code in a general module:

Option Explicit
Sub testme()
Dim myDD As DropDown
Dim wks As Worksheet
Dim DestCell As Range
Dim myDDChoice As String

With ActiveSheet
Set myDD = .DropDowns(Application.Caller)
myDDChoice = myDD.List(myDD.ListIndex)

Select Case LCase(myDDChoice)
Case Is = "old", "new"
Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myDDChoice)
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Missing worksheet: " & myDDChoice
Exit Sub
End If
With wks
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myDD.TopLeftCell.EntireRow.Copy _
Destination:=DestCell
Case Else
Beep
End Select
End With

End Sub

(Rename it to something meaningful!)

Then right click on each dropdown and assign each this macro.

========
You may want to think about having only one dropdown in the worksheet--put it in
row 1 and then Windows|freeze panes to make sure that Row 1 is always visible.
Then you could use the activecell to determine which row to copy.

This line:
myDD.TopLeftCell.EntireRow.Copy _
Destination:=DestCell

would change to:
activecell.EntireRow.Copy _
Destination:=DestCell

Might make things a lot easier for you as the author.
 
Back
Top