如果匹配条件，则搜索表的名称，然后复制具有格式的粘贴值? [英] Search for sheets' name if matched condition then copy paste value with format?

问题描述

更新:我能够解决此问题.我只记得VBA使用*和?呈现字符串'If Sheet.name Like"* CopyA "'

Update: I was able to solve this problem. I just remembered VBA use * and ? to present the string 'If Sheet.name Like "*CopyA"'

我要使副本的特殊值具有所有工作表的原始格式，其中包含单词:"CopyA"，例如"Apple CopyA"，"Mango 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"

我可以使用宏记录来查看VBA如何创建工作表并复制特殊内容，如下面的类似示例.

I can use the macro record to see how VBA create sheet and copy special like similar example below.

我在搜索工作表名称时遇到了麻烦-我发现的所有示例要么我必须知道确切的名称(与宏记录相同)，要么我必须制作所有工作表的副本(遍历所有工作表和副本，无论如何)名字是).

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

如何搜索工作表的名称?在这种情况下，任何具有"CopyA"的工作表吗?

How can I search for sheet's name? In this case, any sheet that has "CopyA"?

推荐答案

另一种方法:)您也可以使用Instr().例如

Another way :) You can also use Instr(). For example

对于工作簿中的所有工作表，请使用此

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

对于ActiveSheet，请使用此

Option Explicit

Sub Macro1()
    If InStr(1, ActiveSheet.Name, "CopyA") Then
        '~~ > Your code
    End If
End Sub

这篇关于如果匹配条件，则搜索表的名称，然后复制具有格式的粘贴值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！