如何为每个特定值生成工作表 [英] How to generate sheet for each specific values
问题描述
使用
循环不起作用。 我的目标是从特定单元格创建新工作表 B16
=每个单元格的房屋和其他新工作表
示例:
当用户点击按钮时:
- 一个新的表格创建的标题= B16的价值刚刚在我的第一张表(名称MyFirstSheet)
- 为每个单元格值创建的一些其他工作表包含单词PRIVATE,刚刚在上一张表。
所以结果将是 MyFirstSheet,House,Test1PRIVATE,Test2PRIVATE
....
Sub NewSheetFromTemplate()
Dim SearchRange As Range,c As Range
Dim sht As Worksheet
'特定单元格的新工作表
表格(TEMPLATE)。复制之后:= Sheets(MyFirstSheet)
ActiveSheet.Name = Sheets(MyFirstSheet)。Range(B16)。value
'每个单元格的新工作表包含PRIVATE
与ThiwWorkbook
设置SearchRange = ActiveSheet.Range(B16:D70)
对于每个c在SearchRange
如果右(c.Value,2)=PRIVATE然后
表格(TEMPLATE)。复制之后:= Sheets(MyFirstSheet)
表(MyFirstSheet)Name = c.Value
结束如果
下一个c
结束
结束Sub
问题是:我的第一张表是很好的创建(所以我有 MyFirstSheet,House,
创建),但不是每个单元格的其他表单包含PRIVATE
Excel说ERROR 1004,并在标题TEMPLATE(2)中创建了一个表格
如果我正确理解问题那么你只需要更改行
如果右(c.Value,2)=PRIVATE然后
to
如果UCase(右(c.Value,7))=PRIVATE然后
这是因为长度单词私人是7个字符而不是2.此外,我使用 UCASE
确保它也会找到一个匹配,如果私人是w带着不同的帽子。
I include some code in my project and I have something I do not understand. My With
loop doesn't works.
My goal is to create new sheet from a specific cell B16
=House and other new sheet for each cells contains PRIVATE
word.
Example: When user click on button: - One new sheet created with title=Value of B16 just after my first sheet (name MyFirstSheet) - Some other sheets created for each cells values contains word PRIVATE, just after the previous sheet.
So the result will be MyFirstSheet, House, Test1PRIVATE, Test2PRIVATE
....
Sub NewSheetFromTemplate()
Dim SearchRange As Range, c As Range
Dim sht As Worksheet
'New sheet for a specific cell
Sheets("TEMPLATE").Copy After:=Sheets("MyFirstSheet")
ActiveSheet.Name = Sheets("MyFirstSheet").Range("B16").Value
'New sheet for each cell contains PRIVATE
With ThiwWorkbook
Set SearchRange = ActiveSheet.Range("B16:D70")
For Each c In SearchRange
If Right(c.Value, 2) = "PRIVATE" Then
Sheets("TEMPLATE").Copy After:=Sheets("MyFirstSheet")
Sheets("MyFirstSheet").Name = c.Value
End If
Next c
End With
End Sub
The problem is: My first sheet is well created (so i have MyFirstSheet, House,
created) but not others sheet for each cell contains "PRIVATE"
Excel say ERROR 1004, and created a sheet in title TEMPLATE (2)
If I understand the question correctly then you merely need to change the line
If Right(c.Value, 2) = "PRIVATE" Then
to
If UCase(Right(c.Value, 7)) = "PRIVATE" Then
That's because the length of the word "private" is 7 characters and not 2. Furthermore, I am using UCASE
to ensure that it will also find a match if private is written with different caps.
这篇关于如何为每个特定值生成工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!