使用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.These记录的前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:现在您已经根据您的注释进行了筛选，您可能需要尝试如下：

定义一个变量，以从源variable获取最后一行数据。类似于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

测试和工作:)