CODEWITHSUNDEEP
vbaexcelmacros
bgaard
bgaard

Reputation: 142

VBA Handeling of Excel Cell containing both Text and Formula-Value

I just started working with VBA for Excel and I ran into a problem I can't find the solution to. I want to make a macro that makes a copy of a sheet and renames the copy to what is specified in cell B8. Now, Cell B8 contains a string and a value (based on a formula) illustrated below.

Content of Cell B8

enter image description here

How do I get VBA to use the name (string and number) as the name of the new sheet? 

Sub NewFunction()

Dim counter As Integer

Sheets(1).Copy After:=Sheets(1)

ActiveSheet.name = Sheets(1).Range("B8").CStr

End Sub

Thanks in advance!

Upvotes: 1

Views: 210

Answers (2)

user3598756
user3598756

Reputation: 29421

you were almost there

Sheets(1).Copy After:=Sheets(1)
ActiveSheet.name = Replace(Replace(Replace(Replace(Range("B8"), "\", "-"), "/", "-"), "?", "-"), "*", "-")

where

  • after Copy() method of Worksheet object, the new worksheet becomes the active one

  • the VBA Replace() method chain is to assure you're not using forbidden characters for your new worksheet name

there remains the issue of possible duplicate names, which can be handled via a specific Function you can find around here if you search for

Upvotes: 0

Rdster
Rdster

Reputation: 1872

For one, you can't use the "/" in the sheet name. It might error, but Excel will just ignore it.

Secondly, you are changing the name on the wrong sheet. You added a new sheet and then referred to the old sheet.

Thirdly, no need to use .CStr as the value of the cell is what you want, and since that is the default property, no need to use .Value either.

Upvotes: 1

Related Questions