Excel VBA按钮(将单元格复制到另一张工作表中的新行) [英] Excel VBA button (copy cells to a new row in another sheet)
问题描述
我需要从表单Form上的表格中复制一些单元格,然后将其粘贴到Activity表单上的新行中。
分解它:
单击按钮时:
-单元格 B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,将在活动工作表(表格)上选择E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22,然后进行复制。
-复制的单元格粘贴到另一张纸上(活动),然后粘贴到新行上(类似于A +1)
远:
Private Sub CommandButton1_Click()
Sheets( Form)。选择
Range( B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22)。选择
选择。复制
Sheets(活动)。选择
If Sheets(活动).Range( A9)=然后
Sheets(活动).Range( A9)。PasteSpecial Paste:= xlPasteValues
其他
Sheets( Activities)。Range( A10)。选择
Selection.End(xlUp)。选择
ActiveCell.Offset(1,0)。选择
Selection.PasteSpecial Paste:= xlPasteValues
End if
结束子
但是它不能正常工作,而且我还没有弄清A + 1。 / p>
有帮助吗?
谢谢
首先,你守如果没有必要,请避免使用 Select
语句(它会不必要地引发事件等)。
我的方法是:
Dim rng作为范围
Set rng = Sheets( Novo Pedido)。Range( B2,B3 ,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22)
每个单元格在rng
'中,您将复制到另一张工作表,向下一行
Sheets( Geral)。Cells(cell.Row + 1,cell.Column).Value = cell.Value
下一个单元格
I need to copy a few cells from a form on sheet Form, then paste them into a new row on sheet Activities.
Breaking it down:
When the button is clicked: - The cells "B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22" will be selected on the active sheet (Form) and copied. - The copied cells are pasted on another sheet (Activities) and pasted on a new row (something like A + 1)
This is what I have so far:
Private Sub CommandButton1_Click()
Sheets("Form").Select
Range("B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22").Select
Selection.Copy
Sheets("Activities").Select
If Sheets("Activities").Range("A9") = "" Then
Sheets("Activities").Range("A9").PasteSpecial Paste:=xlPasteValues
Else
Sheets("Activities").Range("A10").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub
But it's not working properly, and I haven't managed to figure out the A+1 yet.
Any help?
Thanks
First of all, You should avoid Select
statement if it's not necessary (it raises events unnecessarily, etc.).
My approach would be:
Dim rng As Range
Set rng = Sheets("Novo Pedido").Range("B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22")
For Each cell In rng
'here you copy to another sheet, one row lower
Sheets("Geral").Cells(cell.Row + 1, cell.Column).Value = cell.Value
Next cell
这篇关于Excel VBA按钮(将单元格复制到另一张工作表中的新行)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!