Paste to worksheet whose name is selected in a cell

    Hi,

    I'm trying to copy inputted data to a worksheet that the person selects from a drop-down list. I have a worksheet for each training session; people will select the session they want to book on and their details will be added to that worksheet.

    The code I have so far is as follows:

    Code
    [FONT=Arial Unicode MS][size=10][COLOR=#1e2463]Sub CopyThem()[/COLOR][/SIZE][/FONT]
[size=10][COLOR=#1e2463][FONT=Arial Unicode MS]   NextRow = Worksheets("F3").Range("A65536").end(xlup).Row +1[/FONT][/COLOR][/SIZE][size=10][COLOR=#1e2463][FONT=Arial Unicode MS]    [/FONT][/COLOR][/SIZE]
[size=10][COLOR=#1e2463][FONT=Arial Unicode MS]Worksheets("Sheet2").Cells(NextRow, 1).Resize(1, 2).value = Array( _[/FONT][/COLOR][/SIZE][size=10][COLOR=#1e2463][FONT=Arial Unicode MS]        Worksheets("Sheet1").Range("C10").value, _[/FONT][/COLOR][/SIZE][size=10][COLOR=#1e2463][FONT=Arial Unicode MS]        Worksheets("Sheet1").Range("E12").value)[/FONT][/COLOR][/SIZE][FONT=Arial Unicode MS][size=10][COLOR=#1e2463]End Sub[/COLOR][/SIZE][/FONT]



    Instead of sheet2, I need the formula to use the value that the person has selected in sheet1 cell C1.

    Re: paset to worksheet whose name is selected in a cell


    I have found a bit of code on google to select the worksheet based on the value in cell F2, but now get an error. Could someone help me sort the code please, it's hopefully quite easy if you know what you're doing. The error message I get is 'code 9 - subscript out of range'

    Code
    Sub Button1_Click()
strWsName = Sheet1.Range("F2")
Worksheets(strWsName).Select
    NextRow = Worksheets("F2").Range("A65536").End(xlUp).Row + 1
    Worksheets(strWsName).Cells(NextRow, 1).Resize(1, 2).Value = Array( _
        Worksheets("Sheet1").Range("C10").Value, _
        Worksheets("Sheet1").Range("E12").Value)
End Sub

    Re: paset to worksheet whose name is selected in a cell


    You probably mean:


    Code
    NextRow = Worksheets([B][U]Range("F2")[/U][/B]).Range("A65536").End(xlUp).Row + 1

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: paset to worksheet whose name is selected in a cell


    I've hit upon another error now I've built the workbook. I'm using a vlookup table to populate the sheet name value in cell F2. Does Excel read the value of the cell as the formula within it rather than the displayed value? If so is there a modification I can make to the code to make it use the displayed value?

    Re: paset to worksheet whose name is selected in a cell


    you could also place this in a command button.

    Code
    Dim ws As Worksheets
 Dim sheetname As String
 sheetname = UserForm1.ComboBox1.Text " Put your form # and box here.
 Sheets(sheetname).Select
 
   Range("b1:b1500") ' Edit your range here



    Hope this helps someone

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

    Re: paset to worksheet whose name is selected in a cell


    Code
    Range("A1").Value


    will give you the cell contents.


    Code
    Range("A1").Formula


    will give you the formula in the cell.


    Code
    Range("A1").Text


    will give you the cell contents but formatted as you see it in the cell.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login