How do I save the name of a worksheet to a variable in VBA?-CodePudding

So, I am trying to add the name of a worksheet to a new worksheet inside the same workbook.

For example, I will have an inputbox that allows the user to select a cell. I want the name of the worksheet from the cell they selected to be saved to a variable.

That variable will then be called on a different worksheet, entering the name of the worksheet to an empty cell.

Sub sheets()

Dim myCell As Range
Dim current As Worksheet

Set myCell = Application.InputBox( _
    prompt:="Select a cell", Type:=8)

current = ActiveSheet

MsgBox current

End Sub

From the above code, I have an inputbox, when the user selects the cell, I am trying to assign a variable "current" to the activesheet, then I run a msgbox to check if the variable has been assigned correctly. It runs an "object variable not set" error. Does anyone know what I can do to tackle this problem, please?

CodePudding user response：

Since current is a Worksheet object, it needs to be assigned with Set:

Set current = ActiveSheet

However, since it is an object the next line will fail and also needs to be changed:

MsgBox current.Name

If you only want to save the name, you need to change the type of current from Worksheet to String and assign it with something like ActiveSheet.Name.