包含文本和公式值的Excel单元格的VBA处理

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.

我刚开始为Excel使用VBA，遇到了一个无法解决的问题。我想要创建一个宏来复制一个表单并将其重命名为B8中指定的内容。现在，Cell B8包含一个字符串和一个值(基于一个公式)。

Content of Cell B8

内容的细胞B8

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

如何让VBA使用名称(字符串和数字)作为新表的名称?

Sub NewFunction()

Dim counter As Integer

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

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

End Sub

Thanks in advance!

提前谢谢!

2 个解决方案

#1

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

首先，不能在表名中使用“/”。它可能会出错，但是Excel会忽略它。

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.

第三，不需要使用. cstr作为单元格的值，因为它是默认属性，所以也不需要使用. value。

#2

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

工作表对象的Copy()方法之后，新的工作表成为活动的工作表
the VBA Replace() method chain is to assure you're not using forbidden characters for your new worksheet name

VBA Replace()方法链是为了确保新工作表名称不使用禁止字符

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

仍然存在可能重复名称的问题，可以通过一个特定的函数来处理，如果您在这里搜索的话，可以在这里找到这个函数

#1