vba 如果条件匹配，则搜索工作表的名称，然后使用格式复制粘贴值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10670402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search for sheets' name if matched condition then copy paste value with format?
提问by NCC
Update: I was able to solve this problem. I just remembered VBA use * and ? to present the string 'If Sheet.name Like "*CopyA"'
更新：我能够解决这个问题。我只记得 VBA 使用 * 和 ? 呈现字符串 'If Sheet.name Like "* CopyA"'
I would like to make a copy special value with original format of the sources of all sheets contains the word: "CopyA" for example "Apple CopyA","Mango CopyA"
我想使用包含单词的所有工作表源的原始格式复制特殊值：“CopyA”例如“Apple CopyA”，“Mango CopyA”
I can use the macro record to see how VBA create sheet and copy special like similar example below.
我可以使用宏记录来查看 VBA 如何创建工作表并复制特殊内容，如下面的类似示例。
I am having trouble in the searching for sheet name - All examples I found either I must know exact name (same as Macro record) or I have to make a copy of all sheets (loop though all sheet and copy, no matter the name is).
我在搜索工作表名称时遇到问题 - 我发现的所有示例要么我必须知道确切的名称（与宏记录相同），要么我必须制作所有工作表的副本（循环所有工作表并复制，无论名称是什么） ）。
Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1(2)").Select
Cells.Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
How can I search for sheet's name? In this case, any sheet that has "CopyA"?
如何搜索工作表的名称？在这种情况下，任何带有“CopyA”的工作表？
回答by Siddharth Rout
Another way :) You can also use Instr(). For example
另一种方式:) 您也可以使用Instr(). 例如
For all sheets in the workbook, use this
对于工作簿中的所有工作表，请使用此
Option Explicit
Sub Macro1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If InStr(1, ws.Name, "CopyA") Then
'~~ > Your code
End If
Next
End Sub
For ActiveSheet use this
对于 ActiveSheet 使用这个
Option Explicit
Sub Macro1()
If InStr(1, ActiveSheet.Name, "CopyA") Then
'~~ > Your code
End If
End Sub
