时间戳

首页 > 解决方案 > 在 Excel 中，如何使用 VBA 仅复制具有特定顺序的所需单元格

excel - 在 Excel 中，如何使用 VBA 仅复制具有特定顺序的所需单元格

问题描述

我的代码现在通过路由复制最后一个单元格下方的整个内容。

我不仅要按路线过滤，还要按运输方式过滤-所以是或否。

我也想只将选定的单元格从这里复制到这里。像按照以下顺序建议的图像（例如，另一个工作簿中的单元格 C2 到单元格 A1）。

我的代码如下所示：

Private Sub CommandButton1_Click()
Dim cell As Range

With Workbooks("FromHEre.xlsm").Worksheets("Sheet1")

For Each cell In .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
If cell.Value = "1" Then

.Rows(cell.Row).Copy Destination:=Workbooks("ToHere.xlsx").Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next cell
End With
End Sub

标签： excelvba

解决方案

这绝不是一个完整的答案，它未经测试，但应该让你开始。

阅读评论并调整代码以满足您的需求

Private Sub CommandButton1_Click()
    Dim sourceWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim sourceRange As Range
    Dim sourceFilteredRange As Range

    Dim targetWorkbook As Workbook
    Dim targetSheet As Worksheet
    Dim targetCell As Range

    Dim cell As Range

    Dim sourceLastRow As Long
    Dim targetLastRow As Long

    ' Define source and target objects
    Set sourceWorkbook = Workbooks("FromHere.xlsm")
    Set sourceSheet = sourceWorkbook.Worksheets("Sheet1")
    Set targetWorkbook = Workbooks("ToHere.xlsx")
    Set targetSheet = targetWorkbook.Worksheets("Sheet1")

    ' Get last row of source sheet
    sourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

    ' Get last row of target sheet
    targetLastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    ' Set source range
    Set sourceRange = sourceSheet.Range("A1:H" & sourceLastRow)

    ' Filter source range by route and shipped
    With sourceRange
        .AutoFilter Field:=4, Criteria1:="1"
        .AutoFilter Field:=7, Criteria1:="yes"
    End With

    ' Get filtered range
    Set sourceFilteredRange = sourceRange.AutoFilter.Range

    ' Copy filtered range to target sheet
    sourceFilteredRange.Copy targetSheet.Range("A" & targetLastRow)

End Sub

推荐阅读