将数据添加到列列表底部的下一个空闲行

Question

非常基本的问题，但似乎无法使其正常工作。我正在尝试从"源"单元格获取数据，以复制到第二张工作表"技能摘要"列表的下一个空行中名为"目标"的单元格。提前谢谢。

Sub btnS()
Dim b As Object
Dim r As Long, c As Long
Dim dest As Range, origin As Range
Dim i As Long
Dim lastrow As Long
With Worksheets("Form")
Set b = .Buttons(Application.Caller)          'references button
With b.TopLeftCell                            'returns row and col of button pushed
r = .row
c = .Column + 5
End With
Set origin = .Cells(r, c)
End With

这是不起作用的部分

With Worksheets("Skill Summary")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).row + 1
Set dest = .Cells(lastrow, 2)
End With
dest.Value = origin.Value
End Sub

Answer 1

嗨，您可以使用这种代码。

因为作为我个人的观点，"="不是复制数据的工作。

Sub btnS()
Dim b As Object
Dim r As Long, c As Long
Dim dest As Range, origin As Range
Dim i As Long
Dim lastrow As Long
ThisWorkbook.Sheets("Sheet1").Range("a1:a5").Copy
r = Cells(Rows.Count, "a").End(xlUp).Row + 1
ThisWorkbook.Sheets("Sheet2").Range("a" & Cells(Rows.Count, "a").End(xlUp).Row + 1).PasteSpecial xlPasteAll
End Sub

Answer 2

避免复制和粘贴以减少内存使用并提高 VBA 性能

Set b = .Buttons(Application.Caller)          'references button
With b.TopLeftCell                            'returns row and col of button pushed
r = .row
c = .Column
End With
Set rng2 = Sheets("Skill Summary").Cells(Rows.Count, "A").End(xlUp)
Sheets("Skill Summary").Range("A" & rng2.Row + 1 & ":A" & rng2.Row + 5).Value = Sheets("Form").Range(Cells(r, c), Cells(r, c+5)).Value