vba 通过按下宏按钮将行从一张纸复制并插入到另一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11850874/
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 Insert rows from one sheet to another by pushing a macro button
提问by Dora
I have this workbook: I need to have a macro button that my users can click that say "Click Here to Copy", then i need a code that copies rows number 5-25 of sheet titled "TC-Star Here", and i need the copied rows to be inserted (i need the pasted rows to not auto delete the ones that were there previously, so the inserted rows would have to shift the previous ones down) into another sheet named "Time Cards". The inserted rows, i need to have to code insert them starting at cell A1. So everytime the macro button is clicked, rows are copied, and inserted with the previous data unmodified/deleted.
我有这本工作簿：我需要有一个宏按钮，我的用户可以单击它说“单击此处复制”，然后我需要一个代码来复制标题为“TC-Star Here”的工作表第 5-25 行，然后我需要插入复制的行（我需要粘贴的行不会自动删除以前存在的行，因此插入的行必须将以前的行向下移动）到另一个名为“时间卡”的工作表中。插入的行，我需要编码从单元格 A1 开始插入它们。因此，每次单击宏按钮时，都会复制行，并插入未修改/删除的先前数据。
I have this code so far:
到目前为止我有这个代码：
Sub CopyInfo()
On Error GoTo Err_Execute
Sheet2.Range("A1:F11").Value = Sheet1.Range("A1:F11").Value
Sheet1.Range("A1:F11").Copy
Sheet2.Range("A1").InsertCopiedCells
Err_Execute:
MsgBox "All have been copied!"
End Sub
But everytime the button is clicked, it pastes the rows over the existing rows.
但是每次单击按钮时，它都会将行粘贴到现有行上。
Please Help.
请帮忙。
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Sub CopyInfo()
On Error GoTo Err_Execute
Sheet1.Range("A1:F11").Copy
Sheet2.Range("A1").Rows("1:1").Insert Shift:=xlDown
Err_Execute:
If Err.Number = 0 Then MsgBox "All have been copied!" Else _
MsgBox Err.Description
End Sub
回答by DGH
This line
这条线
Sheet2.Range("A1:F11").Value = Sheet1.Range("A1:F11").Value
Sheet2.Range("A1:F11").Value = Sheet1.Range("A1:F11").Value
is setting the value of those cells on Sheet2 to the values on Sheet1 - that is, it's copying the values over and replacing what was there. THEN, you're doing your copy-insert operation.
正在将 Sheet2 上这些单元格的值设置为 Sheet1 上的值 - 也就是说，它正在复制值并替换那里的值。然后，您正在执行复制插入操作。
Delete/comment out that line and see what happens.
删除/注释掉该行，看看会发生什么。
Also, I believe the copy area and the paste area have to be the same size. Try making it Sheet2.Range("A1:F11").InsertCopiedCells
另外，我相信复制区域和粘贴区域的大小必须相同。尝试制作Sheet2.Range("A1:F11").InsertCopiedCells
回答by HelpNeeded
Simple - Use a loop (Also, turn off ScreenUpdating to greatly speed up the processing time) The following will copy all rows from the sheet named "Sheet2" over to the same row in sheet named "Sheet1"
简单 - 使用循环（另外，关闭 ScreenUpdating 以大大加快处理时间）以下将复制名为“Sheet2”的工作表中的所有行到名为“Sheet1”的工作表中的同一行
Sub CopyOver()
Application.ScreenUpdating = False
For j = 1 To 1560 'Or however many rows u have
On Error GoTo Err_Execute
Sheets("Sheet2").Rows(j).Copy
Sheets("Sheet1").Rows(j).Insert Shift:=xlDown
Next j
Application.ScreenUpdating = True
Err_Execute:
If Err.Number = 0 Then MsgBox "All have been copied!" Else _
MsgBox Err.Description
End Sub
回答by Han
Thx for the post dude~, each answer provider solve my problem~.
Here are my coding use in excel, for each selection will insert to Sheet 2,
谢谢发帖人~，每个答案提供者都解决了我的问题~。
这是我在 excel 中的编码使用，对于每个选择将插入到工作表 2，
Private Sub CommandButton2_Click()
Selection.Copy
Sheet2.Range("A4").Rows("1:1").Insert Shift:=xlDown
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub
回答by kumar
Sub Test()
With ActiveSheet
lastrow = .Cells(.rows.Count, "A").End(xlUp).Row
lastrow = lastrow - 1
MsgBox lastrow
End With
End Sub
