How to use result of input box as a worksheet name in VBA code

Question

I'm really new at VBA and have learned what I know so far from internet searches, but I cannot find a resolution to my issue.

I have two workbooks, one with information on all of my company's current projects and another with just the active projects. The Active Projects workbook is where we store all the documents that need reviewing for each project. Each project has it's own worksheet.

When I create a new worksheet in the Active Projects workbook, I would like to use a macro to fill in the relevant project information from the All Projects Workbook.

I have seen code that copies cells and ranges from one workbook to another, but they have the sheet names hard coded in. Like this:

'Copy range to in selected row to clipboard Workbooks("All Project.xlsx").Worksheets("All Open").Range("B" & (ActiveCell.Row)).Copy

'PasteSpecial to paste values, formulas, formats, etc. Workbooks("Active Projects.xlsm").Worksheets(InputBoxValue).Range("A2").PasteSpecial Paste:=xlPasteValues

I thought to use an input box to ask for the worksheet name where the copied data would be pasted, but after hours of research, I cannot find out to use the result of the input box for the worksheet name.

Thank you for furthering my VBA education

Exactly as you've put it... get an input from the user and use that within `Worksheets`. — BigBen, Apr 29 '20 at 20:50
Use a comboxbox populated with the sheet names so they can't enter the wrong thing. — HackSlash, Apr 29 '20 at 22:07

score 0 · Answer 1 · answered Apr 29 '20 at 20:58

0

FYI - this is at high risk of someone putting in the wrong sheet name given the nature of free-form text. That is something you will need to handle on your end so I suggest you look up:

How to check if a sheet given name exists on a book
How to loop a InputBox until an acceptable input is given

Dim Sheet_Name As String

'Get Input
Sheet_Name = Application.InputBox("Enter Sheet Name", Type:=2)

'Use Input
MsgBox ThisWorkbook.Sheets(Sheet_Name).Name

answered Apr 29 '20 at 20:58

urdearboy

14,439
5
28
58

Example of checking if a sheet exits [HERE](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) – urdearboy Apr 29 '20 at 21:00

score 0 · Answer 2 · edited Apr 29 '20 at 21:33

0

Dim InputBoxValue As String
InputBoxValue = InputBox("Enter sheet name")

'PasteSpecial to paste values, formulas, formats, etc. 
Workbooks("Active Projects.xlsm").Worksheets(InputBoxValue).Range("A2").PasteSpecial Paste:=xlPasteValues

edited Apr 29 '20 at 21:33

urdearboy

14,439
5
28
58

answered Apr 29 '20 at 21:01

kal

5
2

score 0 · Answer 3 · answered Apr 30 '20 at 08:17

Below code will rename active sheet :

Sub Rename_Worksheet()

Dim Str As String
Dim Ws As Worksheet
Set Ws = ActiveSheet

Str = InputBox("Please provide new name for Worksheet : " & Ws.Name)

Ws.Name = Str

MsgBox "Worksheet renamed successfully to " & Str

End Sub

How to use result of input box as a worksheet name in VBA code

3 Answers3