使用VBA将动态范围复制并粘贴到Excel中的新工作表 [英] Copy and Paste dynamic ranges to new sheet in Excel with VBA
问题描述
我是宏写作的新手,我需要一些帮助.
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.
- 我要复制A2-A列中的最后一个数据条目,并将其粘贴到Sheet2的A1中
- 我要复制B2-A列中的最后一个数据条目,并将其粘贴到Sheet2的K1中
- 我要复制C2-A列中的最后一个数据条目并将其粘贴到Sheet2上的C1中
- 我要复制D2-A列中的最后一个数据条目,并将其粘贴到Sheet2的D1中
- 然后从工作表2中,我要复制A1:KXXXX(到A列的最后一个条目)并将其保存在剪贴板上以粘贴到其他应用程序中
这是我的代码,我已经尝试过...(我知道这只是用于复制列A,但是我被卡在那里了.)
Here is my code, I have tried... (I know this is just for copying column A, but I got stuck there.)
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
推荐答案
请尝试执行此操作.假设您说粘贴代码有错误,而我仍在使用该代码,那么我认为您仍然会有错误.发布错误消息.希望我们能弄清楚.
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
我一次复制了所有4列,因为这样做会快得多,但它假定各列的长度相同.如果那不是真的,则需要一次复制一个.
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.
数据应位于宏末尾的剪贴板中.
The data should be in the clipboard at the end of the macro.
由于确实不需要"wsIn.Activate",因此我将其删除.糟糕!我只是注意到您想要在不同的列中输出.我会努力的.
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.
这篇关于使用VBA将动态范围复制并粘贴到Excel中的新工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!