VBA - variable worksheet name user defined

Question

Wondering if anyone can help as I've tried several times to create this functionality within excel.

Summary - The end user will place the applicable rear in a particular cell within designated cell on worksheet and push a macro button to copy last years holiday tracker and create a new worksheet, subsequently renaming the copied worksheet.

Problem - when trying to declare the applicable worksheet variable worksheet name in question I get a type mismatch error.

Appreciate if someone can help?

The below code is stumbling block when declaring the sheet in question.

Sub test()

Dim ws as worksheet
Dim wsn as string

wsn = sheets("procedures").range("a1").value
Set ws = wsn

ws.activate

End sub

Darren Bartrup-Cook · Accepted Answer · 2016-09-30 10:02:17Z

1

Wow, that's wordy.

will place the applicable rear in a particular cell within designated cell on worksheet

What's that mean?

Are you trying to add a worksheet and name it using the value in cell A1?
This will do the job providing A1 contains text that can be used as a sheet name.

Sub test()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets.Add

    ws.Name = Sheets("procedures").Range("a1")

    ws.Activate

End Sub

Edit:
This code will copy the sheet that is named in range A1, give it a new name and set a reference to it.

Sub test()

    Dim ws As Worksheet
    Dim wsn As String

    With ThisWorkbook
        wsn = .Worksheets("procedures").Range("a1")
        'Code to check the name is valid & the worksheet exists.
        '....

        'Unfortunately you can't set a reference while copying,
        'so copy it to before the first sheet and then reference the first sheet.
        .Worksheets(wsn).Copy Before:=.Worksheets(1)
        Set ws = .Worksheets(1)
    End With

    ws.Name = "Some Other Sheet Name"

    ws.Activate

End Sub

edited Sep 30, 2016 at 10:02

answered Sep 30, 2016 at 9:32

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Skip Intro Over a year ago

I presume that 'rear' is a typo for 'Year'

Darren Bartrup-Cook Over a year ago

Ah yes, I get it now. :)

Chewy1985 Over a year ago

Correct, massive typo! Lol

Chewy1985 Over a year ago

All I need the coding to do at this point is select or activate the user defined worksheet and copy it. So yes cell A1 would contain the name of the worksheet in question.

Darren Bartrup-Cook Over a year ago

Do you have a sheet called procedures and a text that can be used as a sheet name in range A1 of procedures (error - you typed an invalid name), or is there already a sheet using that name (error - cannot rename sheet)?

|

Collectives™ on Stack Overflow

VBA - variable worksheet name user defined

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related