vba - 使用 excel-vba 宏从前 6 行的特定列复制和粘贴信息，从一个工作簿到另一个工作簿

Question

因此，我需要从一个工作簿的特定列中复制信息，并将此信息粘贴到另一个工作簿的特定列中。我通过编写以下代码成功地做到了这一点:

Sub test()

 Dim wb As Workbook
 Dim mysh As Worksheet
 Dim sourceColumn As Range
 Dim targetColumn As Range
 Dim i As Long


 Set wb = Workbooks("WorkbookA.xlsm")

    'Above code is same as yours

    Set mysh = wb.Sheets(1) 'if only one sheet, use loop otherwise


           mysh.Range("J1").AutoFilter Field:=10, Criteria1:=">=" & Date



Application.ScreenUpdating = False


Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Columns("D")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Columns("B")

sourceColumn.Copy Destination:=targetColumn


Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Columns("C")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Columns("C")

sourceColumn.Copy Destination:=targetColumn

Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Columns("G")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Columns("D")

sourceColumn.Copy Destination:=targetColumn

Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Columns("J")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Columns("E")

sourceColumn.Copy Destination:=targetColumn


Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Columns("K")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Columns("F")

sourceColumn.Copy Destination:=targetColumn


Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Columns("L")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Columns("G")

sourceColumn.Copy Destination:=targetColumn

Application.ScreenUpdating = True


End Sub

我特别想做的是仅从 WorkbookA 的前 6 个可见记录中复制并粘贴此信息。这些记录不是来自(单元格编号 1 到 6)

目前所有行的信息都被复制和粘贴。

如何以适当的方式修改代码以正确执行此操作？

Answer 1

而不是 columns使用 range定义要复制的单元格数。

Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Range("D1:D6")
Set targetColumn = Workbooks("WorkbookB.xlsm").Worksheets(1).Range("B1:B6")
sourceColumn.Copy Destination:=targetColumn

使用 Columns不推荐，因为它对 VBA 来说是一项耗时的任务。单列可以是一个集合+1M 个单元格。这将花费 Excel 大量时间来处理。
sourceColumn和 targetColumn需要定义为 range
编辑 1:现在您已经根据您的评论进行了过滤，您可能想试试这个:

定义一个变量以从源列中获取数据的最后一行数。
类似于 D 列的内容:

Dim LastRow as Long
With Workbooks("WorkbookA.xlsm").Worksheets(1)
LastRow = .Cells(.Rows.count, "D").End(XlUp).Row
End With

现在我们将从源列中获取可见单元格的范围。这是应用过滤器后仅显示可见单元格的步骤。( 我已排除 D1，因为它应该是列的标题 )

Set sourceColumn = Workbooks("WorkbookA.xlsm").Worksheets(1).Range("D2:D" & lastrow).SpecialCells(xlCellTypeVisible) 

我们将简单地遍历 sourcecolumn 的单元格集合，并告诉它在目标工作簿中仅粘贴 6 个单元格:

Dim counter as integer: counter = 1
With  Workbooks("WorkbookB.xlsm").Worksheets(1)
For each cell in sourcecolumn
if counter = 7 then
Exit for
end if 
.Range("B" & counter) = cell.value
counter = counter + 1 
Next
End With
'Counter = 0 do forget to reset it if you're going to use it for the other columns

测试和工作:)