问Excel VBA筛选列中的值并将信息复制到另一个工作簿中

EN

'Private Sub CommandButt_Click()              ' instrument data sheet
'
'Application.ScreenUpdating = False
'
'Dim CounterQTY, rowcounter, colcounter, lastcell, actcell, nextcell, cnt As Integer
'Dim MySheetName, TagNr As String
'Dim WS As Worksheet
'Dim CHK As Boolean
'Dim wbTarget As Workbook 'workbook where the data is to be pasted
'Dim wbThis As Workbook 'workbook from where the data is to copied
'Dim strName As String   'name of the source sheet/ target workbook
''On Error Resume Next
'
'
''***************************************************************************************
''search lastRow
''***************************************************************************************
'
'Dim LastCellA As Range
'Dim LastCellRowNumber As Long
'
'Set searchWS = Worksheets("Ozone Generator Skid")
'    With searchWS
'        Set LastCellB = .Cells(.rows.Count, "B").End(xlUp)
'        LastCellRowNumber = Application.WorksheetFunction.Max(LastCellB.Row)
'    End With
'
'
'
''***************************************************************************************
''create new sheet
''***************************************************************************************
'
''clear any thing on clipboard to maximize available memory
'Application.CutCopyMode = False
'
''set to the current active workbook (the source book)
'Set wbThis = ActiveWorkbook
'
''get the active sheetname of the book
'strName = ThisWorkbook.Path
'
''open a workbook that has same name as the sheet name (the workbook and the sheet inside it have the same name?) JK: now I think this is saying the file that will have all the valve data sheets in it (wbTarget) will have the name of the file that is being opened from the Vault link below (Ex: "Water Instrument Sheet") and inside that file "Water Instrument Sheet" in the Vault, the sheet name is called "Temp_Datasheet" and is invisible, waiting to become visible. I can just download a copy of a valve and instrument data sheet, move it to my Vault folder, rename the file however I want, and then paste the link here. And since "Temp_Datasheet" is already inside the data sheet file, I need not worry about anything except filling in the accurate ranges for the info below!
'
'Set wbTarget = Workbooks.Open("C:\Degremont Vault\ONA\Templates\Templates Activated by BOM\Instrument Data Sheet.xlsx")
'
''activate the source book
'wbThis.Activate
'
'For i = 8 To LastCellRowNumber
'
'    lastcell = Worksheets("Ozone Generator Skid").Cells(i - 1, 3).Value
'    actcell = Worksheets("Ozone Generator Skid").Cells(i, 3).Value
'
'        If actcell <> lastcell And Not (IsEmpty(actcell)) And Worksheets("Ozone Generator Skid").OLEObjects("CHK" & i).Object.Value = True Then
'
'        rowcounter = 2                                                          ' reset tag field
'        colcounter = 3
'        CounterQTY = 1
'
'        MySheetName = Worksheets("Ozone Generator Skid").Cells(i, 3).Text
'
'        wbTarget.Activate
'            For Each WS In wbTarget.Worksheets
'                Worksheets("Temp_Datasheet").Visible = True
'                If InStr(WS.Name, MySheetName) = 1 Then
'                    CHK = True
'                    cnt = cnt + 1
'                End If
'            Next
'
'            If CHK Then
'                Sheets("Temp_Datasheet").Select                                       ' create new sheet if sheet already exist
'                Sheets("Temp_Datasheet").Copy After:=Worksheets(Worksheets.Count)
'                ActiveSheet.Name = MySheetName & "_" & cnt
'                ActiveSheet.Unprotect
'            Else
'                Sheets("Temp_Datasheet").Select                                        ' create new sheet
'                Sheets("Temp_Datasheet").Copy After:=Worksheets(Worksheets.Count)
'                ActiveSheet.Name = MySheetName
'                ActiveSheet.Unprotect
'            End If
'        Worksheets("Temp_Datasheet").Visible = False
'
'
''***************************************************************************************
''copy and paste specific cells to new sheet
''***************************************************************************************
'        ActiveSheet.Range("C11").Value = wbThis.Worksheets("Ozone Generator Skid").Cells(i, 8).Value          ' MM#
'        ActiveSheet.Range("C2").Value = wbThis.Worksheets("Ozone Generator Skid").Cells(i, 4).Value           ' Tag #
'        ActiveSheet.Range("C1").Value = wbThis.Worksheets("Ozone Generator Skid").Cells(i, 32).Value           ' Material Description
'        ActiveSheet.Range("C12").Value = wbThis.Worksheets("Ozone Generator Skid").Cells(i, 20).Value          ' Manufacturer
'        ActiveSheet.Range("C13").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 19).Value          ' Model #
'        ActiveSheet.Range("D2").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 4).Value           ' Process Location
'        ActiveSheet.Range("C14").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 17).Value          ' Material/Body Material
'        ActiveSheet.Range("C15").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 18).Value         ' Connection
'        ActiveSheet.Range("C16").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 27).Value         ' Power
'        ActiveSheet.Range("C17").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 26).Value         ' Instrument Range
'        ActiveSheet.Range("C18").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 30).Value         ' Design Range
'        ActiveSheet.Range("F18").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 28).Value         ' Alarm
'        ActiveSheet.Range("H18").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 29).Value         ' Interlock
'        ActiveSheet.Range("C19").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 31).Value         ' Options on the Data Sheet corresponds to Oxygen Cleaned from the BOM
'        ActiveSheet.Range("C25").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 34).Value         ' Notes
'        ActiveSheet.Range("C10").Value = 1
'
''***************************************************************************************
''copy and paste information from header to new sheet
''***************************************************************************************
'        ActiveSheet.Range("A28").Value = wbThis.Worksheets("Ozone Generator Skid ").Range("AB5").Value   ' Issued
'        ActiveSheet.Range("A30").Value = wbThis.Worksheets("Ozone Generator Skid ").Range("AD5").Value   ' checked
'        ActiveSheet.Range("A32").Value = wbThis.Worksheets("Ozone Generator Skid ").Range("AF5").Value   ' approved
'        ActiveSheet.Range("C27").Value = wbThis.Worksheets("Ozone Generator Skid ").Range("N2").Value   ' project name
'        ActiveSheet.Range("C29").Value = wbThis.Worksheets("Ozone Generator Skid ").Range("N3").Value   ' project #
'        ActiveSheet.Range("H31").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 35).Value  ' Rev
'        ActiveSheet.Range("F30").Value = wbThis.Worksheets("Ozone Generator Skid ").Cells(i, 3).Value   ' Sheet #
'
'        End If
'
''***************************************************************************************
''copy and paste only Tag # to same specsheet
''***************************************************************************************
'    wbThis.Activate
'
'    If actcell = lastcell And Not (IsEmpty(actcell)) And Worksheets("Ozone Generator Skid").OLEObjects("CHK" & i).Object.Value = True Then
'        colcounter = colcounter + 2
'            If colcounter > 8 Then
'                colcounter = 3
'                rowcounter = rowcounter + 1
'            End If
'        wbTarget.Worksheets(MySheetName).Cells(rowcounter, colcounter).Value = wbThis.Worksheets("Ozone Generator Skid").Cells(i, 4).Value ' Tag #
'        wbTarget.Worksheets(MySheetName).Cells(rowcounter, colcounter + 1).Value = wbThis.Worksheets("Ozone Generator Skid").Cells(i, 8).Value ' Process Location
'        CounterQTY = CounterQTY + 1
'        wbTarget.Worksheets(MySheetName).Range("C10").Value = CounterQTY
'
'    End If
'
'Next i
'
'
'wbThis.Activate
'Application.ScreenUpdating = True
'
''Clear memory
'Set wbTarget = Nothing
'Set wbThis = Nothing
'
'
'End Sub