时间戳

首页 > 解决方案 > 将数据正下方的范围粘贴到从特定单元格开始

excel - 将数据正下方的范围粘贴到从特定单元格开始

问题描述

我想直接粘贴在从特定单元格开始的列中已有的数据下方，但是由于列中已经有数据，我需要从单元格 A7 开始粘贴数据。

有什么建议么？

Dim copyNames1 As Range, pasteNames1 As Range

Set copyNames1 = Workbooks("2019 11 November.xls").Worksheets(3).Columns("F")
Set pasteNames1 = Workbooks("VBA Workbook.xlsm").Worksheets(1).Columns("A").Offset(7, 0)

copyNames1.Copy Destination:=pasteNames1

End Sub

此代码不起作用，因为它返回错误并通过重新定位它弄乱了其余数据。

编辑：这是我尝试过的另一种选择：

'   Activate cell directly below pasted data

Range("A1").End(xlDown).Offset(1, 0).Activate

' CR CARDS
'
'   NAMES

Dim copyNamess As Range, pasteNamess As Range

Set copyNamess = Workbooks("2019 11 November.xls").Worksheets(3).Columns("F")
Set pasteNamess = Workbooks("VBA Workbook.xlsm").Worksheets(1).ActiveCell

copyNames1.Copy Destination:=pasteNames1

End Sub

标签： excelvba

解决方案

试试这个...我添加了工作表变量，使用了调整大小并合并了最后一行。

'Define your variables
Dim cpyws As Worksheet, pstws As Worksheet, copyNames1 As Range, pasteNames1 As Range

'Assign your worksheet variables
Set cpyws = Workbooks("2019 11 November.xls").Worksheets(3)
Set pstws = Workbooks("VBA Workbook.xlsm").Worksheets(1)

'Assign your copy/paste range variables
Set copyNames1 = cpyws.Cells(1, 6).Resize(cpyws.Cells(cpyws.Rows.Count, 6).End(xlUp).Row) 'I like to use resize and incorporate the last row
Set pasteNames1 = pstws.Cells(pstws.Rows.Count, "A").End(xlUp).Offset(1) 'Set start cell to paste your copied range to

    'Copy/Paste
    copyNames1.Copy Destination:=pasteNames1

推荐阅读