copy data from one sheet to another

Mega1

New Member
Joined
Sep 19, 2017
Messages
25
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Code:
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.
 
Upvote 0
Try this

Code:
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
 
Upvote 0
This worked but not quite what i wanted Thanks Alan

the second worked the way i wanted
 
Last edited:
Upvote 0
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.
 
Upvote 0
hello Alan

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

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top