使用 VBA 将动态范围复制并粘贴到 Excel 中的新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42420280/
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
Copy and Paste dynamic ranges to new sheet in Excel with VBA
提问by Jessica Walker
I am new to macro writing and I need some help.
我是宏写作的新手，我需要一些帮助。
I have one sheet and need to copy the columns and reorder them to paste into a software program.
我有一张纸，需要复制列并重新排序以粘贴到软件程序中。
- I want to copy A2 - the last data entry in column A and paste it into A1 on Sheet2
- I want to copy B2 - the last data entry in column A and paste it into K1 on Sheet2
- I want to copy C2 - the last data entry in column A and paste it into C1 on Sheet2
- I want to copy D2 - the last data entry in column A and paste it into D1 on Sheet2
- Then from Sheet 2, I want to copy A1:KXXXX (to the last entry in column A) and save it on the clipboard to paste into the other application
- 我想复制 A2 - A 列中的最后一个数据条目并将其粘贴到 Sheet2 上的 A1 中
- 我想复制 B2 - A 列中的最后一个数据条目并将其粘贴到 Sheet2 上的 K1 中
- 我想复制 C2 - A 列中的最后一个数据条目并将其粘贴到 Sheet2 上的 C1 中
- 我想复制 D2 - A 列中的最后一个数据条目并将其粘贴到 Sheet2 上的 D1 中
- 然后从工作表 2，我想复制 A1:KXXXX（到 A 列中的最后一个条目）并将其保存在剪贴板上以粘贴到另一个应用程序中
Here is my code, I have tried... (I know this is just for copying column A, but I got stuck there.)
这是我的代码，我试过了……（我知道这只是为了复制 A 列，但我被卡在那里了。）
Sub Copy()
aLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A2" & aLastRow).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Thank you so much for your help! Jess
非常感谢你的帮助！杰斯
回答by jce
Try this instead. Given that you said you got an error with the paste code and I am still using that, I think you'll still have an error there. Post the error message. Hopefully we can figure that out.
试试这个。鉴于您说粘贴代码出错并且我仍在使用它，我认为您仍然会在那里出错。贴出错误信息。希望我们能解决这个问题。
Sub copyStuff()
Dim wsIn As Worksheet
Set wsIn = Application.Worksheets("Sheet1")
Dim endRow As Long
wsIn.Activate
endRow = wsIn.Cells(wsIn.Rows.Count, "A").End(xlUp).Row
Dim r As Range
Dim wsOut As Worksheet
Set wsOut = Application.Worksheets("Sheet2")
' column a to column a
Set r = wsIn.Range(Cells(2, 1), Cells(endRow, 1))
r.Copy
wsOut.Range("A1").PasteSpecial xlPasteAll
' column b to column k
Set r = wsIn.Range(Cells(2, 2), Cells(endRow, 2))
r.Copy
wsOut.Range("K1").PasteSpecial xlPasteAll
' column c to column c
Set r = wsIn.Range(Cells(2, 3), Cells(endRow, 3))
r.Copy
wsOut.Range("C1").PasteSpecial xlPasteAll
' column d to column d
Set r = wsIn.Range(Cells(2, 4), Cells(endRow, 4))
r.Copy
wsOut.Range("D1").PasteSpecial xlPasteAll
' Copy data from sheet 2 into clipboard
wsOut.Activate
Set r = wsOut.Range(Cells(1, 1), Cells(endRow - 1, 11))
r.Copy
End Sub
My original answer is below here. You can disregard.
我的原始答案在这里。你可以无视。
This should accomplish your first goal:
这应该可以实现您的第一个目标：
Sub copyStuff()
Dim wsIn As Worksheet
Set wsIn = Application.Worksheets("Sheet1")
Dim endRow As Long
endRow = wsIn.Cells(wsIn.Rows.Count, "A").End(xlUp).Row
Dim r As range
Set r = wsIn.range(Cells(2, 1), Cells(endRow, 4))
r.Copy
Dim wsOut As Worksheet
Set wsOut = Application.Worksheets("Sheet2")
wsOut.range("A1").PasteSpecial xlPasteAll
End Sub
I copied all 4 columns at once since that would be much faster but it assumes the columns are the same length. If that isn't true you would need to copy one at a time.
我一次复制了所有 4 列，因为这样会快得多，但它假设列的长度相同。如果不是这样，您将需要一次复制一份。
The data should be in the clipboard at the end of the macro.
数据应位于宏末尾的剪贴板中。
Edit: I removed "wsIn.Activate" since it isn't really needed. Edit 2: Oops! I just noticed you wanted the output in different columns. I'll work on it.
编辑：我删除了“wsIn.Activate”，因为它并不是真正需要的。编辑2：哎呀！我只是注意到你想要不同列中的输出。我会努力的。
回答by VBA Pete
Generally you want to avoid .Selectand .Pastewhen copying values and rather copy by .value = .value:
通常，您希望在复制值时避免.Select和.Paste，而是通过.value = .value以下方式复制：
Sub Copy()
Dim aLastRow As Long
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
aLastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("A1:A" & aLastRow - 1).Value = Sheets("Sheet1").Range("A2:A" & aLastRow).Value
Sheets("Sheet2").Range("K1:K" & aLastRow - 1).Value = Sheets("Sheet1").Range("B2:B" & aLastRow).Value
Sheets("Sheet2").Range("C1:D" & aLastRow - 1).Value = Sheets("Sheet1").Range("C2:D" & aLastRow).Value
clipboard.SetText Sheets("Sheet2").Range("A1:K" & aLastRow - 1).Value
clipboard.PutInClipboard
End Sub
