vba 要粘贴到另一个工作表中的数据的动态范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16198397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic range of data to paste into another sheet?
提问by user1717622
I have a sheet with two tabs:
我有一张带有两个标签的工作表：
on tab 1I have a contiguous block of data in Columns J,K that varies in the number of rows but always starts from J1, K1.
在选项卡 1 上，我在列 J、K 中有一个连续的数据块，其行数有所不同，但始终从 J1、K1 开始。
On tab 2I have a data in Column A only, starting from A1.
在选项卡 2 上，我只有 A 列中的数据，从 A1 开始。
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.
我正在寻找使我能够动态选择选项卡 1 中的整个数据块的代码，无论多行深。
then paste that block, it starting at the first empty cell in column A in tab 2.
然后粘贴该块，它从选项卡 2 中 A 列的第一个空单元格开始。
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
Your thoughts are appreciated,
您的想法值得赞赏，
Best regards
此致
采纳答案by Rohit Agrawal
A shorter answer will be
一个简短的答案将是
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
Incase K also needs to go to A then code will be
Incase K 也需要去 A 那么代码将是
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
回答by Joseph
Note that when you use the Range() object, you are implicitly referencing the ActiveSheet, which may not be the sheet you think it is. It's always best to explicitly call out the sheet you need to reference.
请注意，当您使用 Range() 对象时，您隐式引用了 ActiveSheet，它可能不是您认为的工作表。最好明确标出您需要参考的工作表。
Try this:
尝试这个：
Sub test()
Application.ScreenUpdating = False
Dim s1 As Excel.Worksheet
Dim s2 As Excel.Worksheet
Dim iLastCellS2 As Excel.Range
Dim iLastRowS1 As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
' get last row of J in Sheet1
iLastRowS1 = s1.Cells(s1.Rows.Count, "J").End(xlUp).Row
' get last AVAILABLE cell to past into
Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)
'copy into sheet2
s1.Range("J1", s1.Cells(iLastRowS1, "J")).Copy iLastCellS2
' get last row of K and copy
iLastRowS1 = s1.Cells(s1.Rows.Count, "K").End(xlUp).Row
Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)
s1.Range("K1", s1.Cells(iLastRowS1, "K")).Copy iLastCellS2
Application.ScreenUpdating = True
End Sub
回答by user1717622
this was the code i needed many thanks
这是我需要的代码，非常感谢
Sub test()
Application.ScreenUpdating = False
Dim s1 As Excel.Worksheet
Dim s2 As Excel.Worksheet
Dim iLastCellS2 As Excel.Range
Dim iLastRowS1 As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
' get last row number of J in Sheet1
iLastRowS1 = s1.Cells(s1.Rows.Count, "J").End(xlUp).Row
' get last AVAILABLE cell to past into
Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)
'copy&paste into sheet2
s1.Range("J1", s1.Cells(iLastRowS1, "K")).Copy iLastCellS2
Application.ScreenUpdating = True
End Sub
