copy data from one sheet to another

[Mega1]

i would like users to enter data into sheet named Menu in a column B2 to B12 in B13 i have a drop down menu of sheets
i would like the data to be copied to the sheet in the drop down menu in rows

 

[alansidman]

Option Explicit


Sub Mega1()
    Dim ws As String
    ws = Range("B13").Value2
    Dim lr As Long
    Dim rng As Range
    Set rng = Sheets("Sheet1").Range("B2:B12")
    rng.Copy
    lr = Sheets(ws).Range("A" & Rows.Count).End(xlUp)
    Sheets(ws).Range("A" & lr + 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Pasted to next open row in designated sheet.

 

[DanteAmor]

Try this

Sub Copy_Data()
    Dim sh As Worksheet, exists As Boolean, wName As String
    
    Application.ScreenUpdating = False
    wName = Range("B13").Value
    If wName = "" Then
        MsgBox "Enter sheet name"
        Exit Sub
    End If
    If Range("B2").Value = "" Then
        MsgBox "Fill cell B2"
        Exit Sub
    End If
    '
    exists = False
    For Each sh In Sheets
        If LCase(sh.Name) = LCase(wName) Then
            exists = True
            Exit For
        End If
    Next
    If exists Then
        Range("B2:B12").Copy
        Sheets(wName).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Application.CutCopyMode = False
        MsgBox "Done"
    Else
        MsgBox "The sheet does not exist"
    End If
End Sub

 

[Mega1]

This worked but not quite what i wanted Thanks Alan

the second worked the way i wanted

 

[DanteAmor]

This worked but not quite what i wanted Thanks Alan

the second worked the way i wanted

I'm glad to help you. Thanks for the feedback.

 

[alansidman]

Curious, what did it not do that you wanted it to do. Want to make sure I understand what you asked for in Post #1 that I did not give you.

 

[Mega1]

hello Alan

when yours did the copy from b2 to b12 it copied it that way i wanted the data in rows

 

[alansidman]

Ah, missed that. Thanks for clarifying.