Vba Worksheet Name With Space

Up to this point you should realize that when you refer to a cell on a worksheet where the sheet name carries space you need to wrap the worksheet name with single quote to make the formula work.

Vba worksheet name with space. I have a sheet name in excel that is dcidata. Click on the sheet name in the project explorer that you want to rename. This will display the activesheet name in a message box. Experiment 1 using hyperlink function.

However everything i ve tried with spaces doesn t work. Sub parse names dim thename as string dim spaces as integer do until activecell thename activecell value spaces 0 for test 1 to len thename if mid thename test 1 then spaces spaces 1 end if next if spaces 3 then break space position space position thename spaces 1 else break space position space position thename spaces. This tutorial will cover interacting with sheet names in vba. The sheet name is the tab name that s visible at the bottom of excel.

Sheet names are stored in the name property of the sheets or worksheets object. The above steps would change the name of your worksheet in the vba backend. You may download a sample file to follow along. This macro sets today s date as the name for the current sheet sub nameworksheetbydate changing the sheet name to today s date activesheet name format now dd mm yyyy changing the sheet name to a value from a cell activesheet name activesheet.

Note that you can t have spaces in the name. In the properties pane change the name in the field in front of name. How do i refer to this sheet now that it has a space in it. I m able to set a worksheet name without spaces and manipulate it with out issue.

I have several references to the sheet in vba as dcidata. Let s continue with a few experiments. The following code example sets the name of the active worksheet equal to today s date.