动态范围的数据要粘贴到另一个工作表中? [英] Dynamic range of data to paste into another sheet?

问题描述

我有一个带有两个标签的工作表:

I have a sheet with two tabs:

在选项卡1 上，我在J，K列中有一个连续的数据块，其行数各不相同，但始终从J1，K1开始.

on tab 1 I have a contiguous block of data in Columns J,K that varies in the number of rows but always starts from J1, K1.

在标签2 上，我只有A列中的数据，从A1开始.

On tab 2 I have a data in Column A only, starting from A1.

我正在寻找使我能够在选项卡1中动态选择整个数据块的代码，但是可能有很多行.

I am looking for the code that will enable me to dynamically select the entire block of data in tab 1, however many rows deep that may be.

然后粘贴该块，它从选项卡2中A列的第一个空单元格开始.

then paste that block, it starting at the first empty cell in column A in tab 2.

到目前为止，这是我的尝试:

This is my attempt thus far:

Sub put_there2()
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim LastRowNumber As Long
Dim LastCell As Range
Dim WS As Worksheet

Set r1 = Range("A2:A100") 'Paste Location

Set WS = Worksheets("Sheet1")
With WS                                                 ' sheet in which to measure range of data to be pasted
    Set LastCell = .Cells(.Rows.Count, 10).End(xlUp)
    LastRowNumber = LastCell.Row


End With

Set r2 = Range(Cells(2, 10), Cells(LastRowNumber, 11))       'region to be copied

For Each r3 In r1
    If r3.Value = "" Then
        r2.Copy r3
        Exit Sub
    End If
Next


End Sub

您的想法受到赞赏，

最诚挚的问候

推荐答案

一个简短的答案是

Set ws = Sheets("Sheet1")
ws.Range(ws.Range("J1:K1"), ws.Range("J1:K1").End(xlDown)).Copy
Sheets("Sheet2").Range("A1").End(xlDown).Offset(1,0).Paste

如果K也需要转到A，则代码为

Incase K also needs to go to A then code will be

Set ws = Sheets("Sheet1")

ws.Range(ws.Range("J1"), ws.Range("J1").End(xlDown)).Copy
Sheets("Sheet2").Range("A1").End(xlDown).Offset(1,0).Paste

ws.Range(ws.Range("K1"), ws.Range("K1").End(xlDown)).Copy
Sheets("Sheet2").Range("A1").End(xlDown).Offset(1,0).Paste

这篇关于动态范围的数据要粘贴到另一个工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！