CODEWITHSUNDEEP
excelvba
Chewy1985
Chewy1985

Reputation: 35

VBA - variable worksheet name user defined

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

Upvotes: 2

Views: 2344

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

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

Upvotes: 1

Related Questions