Next Available Row

[McGaffinWorld]

Hello all,

Using the below code, rather then having the information go to Sheet2, I would like to beable to send the information to other steets determined by a dropdown menu. I have my dropdown menu in cell E2 which has the names of the other sheets. Can I set the below code to look for the information that is selected in the dropdown and then send the information to the selected sheet? Currently I have multiple buttons and the below code for each button but a dropdown seems more efficent.
Thanks for the help.

1. Private Sub CommandButton1_Click()</SPAN>
2. Dim c As Long</SPAN>
3. c = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1</SPAN>
4. Range("A2:E2").Copy Worksheets("Sheet2").Range("A" & c)</SPAN>
5. Application.CutCopyMode = False</SPAN>
6. Range("A2:E2").ClearContents</SPAN>
7. Range("A2").Select</SPAN>
8. End Sub</SPAN>

 

[AlphaFrog]

Try something like this. It assumes the "dropdown" is ComboBox1.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]Dim[/color] c      [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] ws     [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        [color=darkblue]Set[/color] ws = Sheets(ComboBox1.Value)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    [color=darkblue]If[/color] ws [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        MsgBox "Cannot locate sheet " & ComboBox1.Value, , "Sheet Not Found"
    [color=darkblue]Else[/color]
        c = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
        Range("A2:E2").Copy ws.Range("A" & c)
        Application.CutCopyMode = [color=darkblue]False[/color]
        Range("A2:E2").ClearContents
        Range("A2").Select
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[McGaffinWorld]

Thanks AlphaFrog, I will try that now.