vba 在宏中复制带有公式的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18342682/
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 cells with formulas in macro
提问by Luis Valencia
I have a excel spreadsheet with 2 sheets. In the first sheet, I got some values, when I click on a button then in the second sheet the following happens 1. a new row is inserted at the top 2. the data from the first sheet is copied.
我有一个包含 2 张纸的 Excel 电子表格。在第一张工作表中，我得到了一些值，当我点击一个按钮然后在第二张工作表中发生以下情况 1. 在顶部插入一个新行 2. 复制第一张工作表中的数据。
However, there are some columns with formulas after the copied cells, and I dont know how to do in vbscript to copy those formulas after your insert a new row.
但是，在复制的单元格之后有一些带有公式的列，我不知道如何在 vbscript 中执行在插入新行后复制这些公式的操作。
Sub Trade1()
Sheets("Trades").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2:R2").Select
Selection.ClearContents
Range("A2").Select
Dim fromRange As Range, toRange As Range
Set fromRange = Sheets("Enter Trade").Range("B2:B20")
Set toRange = Sheets("Trades").Range("A2")
fromRange.Copy
toRange.PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Sub
回答by tigeravatar
After inserting a new row 2 (so that it's contents are now on row 3), I would copy row 3 back to row 2 (basically make a duplicate), and then override the values with the data you want to move from sheet 'Enter Trade' range B2:B20, like so:
插入新的第 2 行（这样它的内容现在位于第 3 行）后，我将第 3 行复制回第 2 行（基本上是复制），然后用要从工作表移动的数据覆盖值 'Enter Trade' 范围 B2:B20，如下所示：
Sub Trade_Button_Click()
Dim btn As Button
Dim wsEntry As Worksheet
Dim wsTrades As Worksheet
Set wsEntry = Sheets("Enter Trade")
Set wsTrades = Sheets("Trades")
Set btn = wsEntry.Buttons(Application.Caller)
wsTrades.Rows(2).Insert
wsTrades.Rows(3).Copy wsTrades.Rows(2)
With Intersect(wsEntry.Range("A2", wsEntry.Cells(Rows.Count, "A").End(xlUp)).EntireRow, wsEntry.Columns(btn.TopLeftCell.Column))
wsTrades.Range("A2").Resize(, .Rows.Count).Value = Application.Transpose(.Value)
End With
Set wsTrades = Nothing
Set wsEntry = Nothing
End Sub
I have uploaded a modified version of your provided workbook here: https://docs.google.com/file/d/0Bz-nM5djZBWYa0R5T2hXMERabjg/edit?usp=sharing
我在此处上传了您提供的工作簿的修改版本：https: //docs.google.com/file/d/0Bz-nM5djZBWYa0R5T2hXMERabjg/edit?usp=sharing
In the modified version, I have mapped all of the Enter buttons to the above macro. When I click on a button, it copies over the correct column's values and also copies the formulas in the rest of the table within sheet 'Trades' successfully.
在修改后的版本中，我已将所有 Enter 按钮映射到上述宏。当我点击一个按钮时，它会复制正确列的值，并成功复制表格“交易”中表格其余部分中的公式。
