vba 根据条件复制某些 Excel 列

Copy certain excel columns based on ones criteria

提问by drblacko

First thing I did was create a button that would copy certain cells using this code:

我做的第一件事是创建一个按钮，可以使用以下代码复制某些单元格：

Worksheets("Sheet1").Range("A:A,B:B,D:D").Copy _ and it worked fine.

Worksheets("Sheet1").Range("A:A,B:B,D:D").Copy _ 并且工作正常。

Second, I found the code that would copy all details in a row based on the criteria of one, in this case if there was an "A" in the "Location" column.

其次，我发现代码会根据一个条件复制一行中的所有详细信息，在这种情况下，如果“位置”列中有一个“A”。

Private Sub ENTIREROW_Click() 'Sub copyrows()

Dim i As Range, Cell As Object

Set i = Range("D:D") 'Substitute with the range which includes your True/False values

For Each Cell In i

   If IsEmpty(Cell) Then
       Exit Sub
   End If

   If Cell.Value = "A" Then
       Cell.ENTIREROW.Copy
       Sheet2.Select 'Substitute with your sheet
       ActiveSheet.Range("A65536").End(xlUp).Select
       Selection.Offset(1, 0).Select
       ActiveSheet.Paste
   End If

Next

End Sub

Private Sub ENTIREROW_Click() 'Sub copyrows()

将 i 变暗为范围，单元格为对象

Set i = Range("D:D") '用包含真/假值的范围替换

对于 i 中的每个单元格

   If IsEmpty(Cell) Then
       Exit Sub
   End If

   If Cell.Value = "A" Then
       Cell.ENTIREROW.Copy
       Sheet2.Select 'Substitute with your sheet
       ActiveSheet.Range("A65536").End(xlUp).Select
       Selection.Offset(1, 0).Select
       ActiveSheet.Paste
   End If

下一个

结束子

My question is, how do I copy all information in the specified columns (A,B,D) where there is an "A" in "Location" in one button.

我的问题是，如何复制指定列（A、B、D）中的所有信息，其中一个按钮的“位置”中有“A”。

Furthermore, this is my example data, the sheet I will actually use this on has 34 columns to copy. Is there a more efficient way of setting a range when you don't want an entire sequence, everything but the data in column C?

此外，这是我的示例数据，我将实际使用的工作表有 34 列要复制。当您不想要整个序列（除了 C 列中的数据之外的所有内容）时，是否有更有效的方法来设置范围？

Thanks in advance and apologies for my explanation skills.

在此先感谢并为我的解释技巧道歉。

采纳答案by Mark Fitzgerald

One way maybe to:

一种方法可能是：

• filter your source
• hide column C
• copy the result using .PasteSpecial xlPasteValuesinto the destination
• Unhide column C on the source sheet
• remove the autofilter

• 过滤你的来源
• 隐藏 C 列
• 将结果使用复制.PasteSpecial xlPasteValues到目标中
• 取消隐藏源工作表上的 C 列
• 删除自动过滤器

Using xlPasteValuesonly pastes the visible cells from the source - so no column C

使用xlPasteValues只粘贴来自源的可见单元格 - 所以没有列 C

The code then looks like this: .

代码如下所示：

Sub CopyRows()
    With Sheets(1).Range([A2], [A2].SpecialCells(xlLastCell))
        [A1].AutoFilter
        .AutoFilter Field:=4, Criteria1:="A"
        [C:C].EntireColumn.Hidden = True
        .Copy
        [C:C].EntireColumn.Hidden = False
    End With
    With Sheets(2)
        If .Cells(Sheets(2).Rows.Count, 1).End(xlUp) = "" Then 'it's a clean sheet
            .Cells(Sheets(2).Rows.Count, 1).End(xlUp).PasteSpecial Paste:=xlPasteValues
        Else
            .Cells(Sheets(2).Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    End With
    Application.CutCopyMode = False
    Sheet1.[A1].AutoFilter
End Sub