在新的工作表VBA中复制/粘贴黄色突出显示细胞

Question

我正在尝试完成此操作。

此宏应打开一个工作簿（工作簿的名称总是更改，并且总有一个表格要处理）。这有效。

设置整个纸张的范围；工作正常。

并在整个纸上搜索以黄色突出显示的单元格，然后将这些单元格复制到新的纸上...这是我需要帮助的地方！

我真的是VBA的新手，这就是我到目前为止所拥有的：

Option Explicit
Sub test3()
    Dim data As Variant
    Dim rngTemp As Range
    Dim cell As Range
    '//open Workbook
    data = Application.GetOpenFilename(, , "Open Workbook")
    Workbooks.Open data

    '// set Range ( Whole Sheet)
    Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not rngTemp Is Nothing Then
        Range(Cells(1, 1), rngTemp).Select
    End If
    '// Search for Yellow highlighted Cells and (if you find one)
    '// Copy Cell B1 + the 3rd Cell in the column (of the highlighted Cell) + the value highlighted Cell
    '// and paste in new Sheet
        For Each cell In rngTemp.Cells
            If rngTemp.Interior.ColorIndex = 6 Then
                cell.Select
                Selection.Copy
                Sheets.Add
                Range("A1").PasteSpecial
                Application.CutCopyMode = False
            End If
        Next
End Sub

Answer 1

 Sub test3()
    Dim wbName As string
    Dim rngTemp As Range
    Dim r As Range
    DIM TARGETSHEET AS WORKSHEET
    DIM TARGET AS RANGE
    '//open Workbook
    wbName = Application.GetOpenFilename(, , "Open Workbook")
    if  wbName = "" or wbname = "CANCEL" then exit sub
    Workbooks.Open wbname

    '// set Range ( Whole Sheet)
    Set rngTemp = Activesheet.usedrange
    SET TARGETSHEET = ACTIVEWORKBOOK.WORKSHEETS.ADD()
    SET TARGET = TARGETSHEET.RANGE("A1")  
'// Search for Yellow highlighted Cells and (if you find one)
    '// Copy Cell B1 + the 3rd Cell in the Column (of the highlighted Cell) + the value highlighted Cell
    '// and paste in new Sheet
        For Each r In rngTemp
            If r.Interior.ColorIndex = 6 Then

                TARGET = rngtemp.parent.range("B1")
                TARGET.OFFSET(0,1) = r
                TARGET.OFFSTE(0,2) = rngtemp.parent.cells(3,r.column)
       'I've assumed you want them across the first row
                SET TARGET = TARGET.OFFSET(1,0)
            End If
        Next r
       End Sub