vba 快速复制和粘贴公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34857513/
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 formulas quickly
提问by user7004
I have been trying to write a simple code that copies the value from one cell and paste its formula into all the cells in one column (There are several cells, around 3000). The code works, but it takes around 30 min to run, so it's not ok for me. I also tried to let the value of the formula without "=" and then use the replace command, but it does not work as well. Anyone could help me with that in order to run the macro in 1 min? This is the part of my code that I try to do that:
我一直在尝试编写一个简单的代码，从一个单元格复制值并将其公式粘贴到一列中的所有单元格中（有几个单元格，大约 3000）。代码有效，但运行大约需要 30 分钟，所以对我来说不太好。我还尝试让公式的值不带“=”，然后使用替换命令，但效果不佳。任何人都可以帮助我在 1 分钟内运行宏？这是我尝试这样做的代码的一部分：
sub copy_paste
Worksheets("Formatar").Range("H1:L1").Copy
Worksheets("Formatar").Range("H3").PasteSpecial xlValue
Worksheets("Formatar").Range("H3:L3").Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial xlFormulas
end sub
回答by Elbert Villarreal
Tell me if this help you...
告诉我这是否对你有帮助...
Sub copy_paste()
Worksheets("Formatar").Range("H1:L1").Copy 'Copy from row 1
Worksheets("Formatar").Range("H3").PasteSpecial xlPasteValues 'paste the values to row 3
Worksheets("Formatar").Range("H3:L3").Copy 'here you copy that (the values)
Range(Selection, Selection.End(xlDown)).Select you select eveything from row3
Selection.PasteSpecial xlPasteValues 'and paste it... but you copy just values from 3!
End Sub
End then you paste ir over the first occurrence and you lost data.
结束然后您将 ir 粘贴到第一次出现并丢失数据。
Here is my suggest.
这是我的建议。
Sub copy_paste()
Dim sht As Worksheet
Dim r
Dim H
Dim L
Set sht = Sheets("Formatar") 'store the sheet
sht.Activate 'activate it!
Range("H1:L1").Copy
Range("H3").PasteSpecial xlPasteFormulas 'Paste the formula
Range("H3:L3").Copy 'then copy again
H = Range("H1").Column 'Just to take the number of the columns H and L
L = Range("L1").Column
r = Range("H3").End(xlDown).Row - 1 'Take the number of the last blank row.
Range(Cells(3, H), Cells(r, L)).PasteSpecial xlPasteValues
'Here you paste values, of if you need the
'formula use this: xlPasteFormulas
Application.CutCopyMode = False 'never forget this...
End Sub
Edit
编辑
May be this could help...
也许这可以帮助...
'Application.Calculation = xlManual
Sub copy_paste()
Dim sht As Worksheet
Dim r
Dim H
Dim L
Set sht = Sheets("Formatar") 'store the sheet
sht.Activate 'activate it!
Range("H1:L1").Copy
Range("H3").PasteSpecial xlPasteFormulas 'Paste the formula
Application.Calculation = xlManual 'Not automatic calculation
Range("H3:L3").Copy 'then copy again
H = Range("H1").Column 'Just to take the number of the columns H and L
L = Range("L1").Column
r = Range("H3").End(xlDown).Row - 1 'Take the number of the last blank row.
Range(Cells(3, H), Cells(r, L)).PasteSpecial xlPasteValues
'Here you paste values, of if you need the
'formula use this: xlPasteFormulas
Application.CutCopyMode = False 'never forget this...
Calculate 'Calculate the whole sheet
Application.Calculation = xlCalculationAutomatic 'return automatic calculation
End Sub
