如何仅从一个Excel工作表复制公式,该工作表可以使用宏动态增长到另一工作表 [英] How to copy only formulas from one excel sheet which can dynamically grow to another sheet using macro
问题描述
我有两个Excel工作表Sheet1和Sheet2.Sheet1是动态Excel工作表,可能有添加列的机会.我已经编码了将列标题从Sheet1动态复制到Sheet2的情况.
I have two excel sheets sheet1 and sheet2.Sheet1 is a dynamic excel sheet,there may be chance of adding columns.I have already coded to copy column heading from sheet1 to sheet2 dynamically.
Sheet1:
Prdct Id PrdctQty Unitprice PrdctQty
1 5 10 50
2 10 10 100
sheet2:
Prdct Id PrdctNme Unitprice PrdctQty
当我打开工作表2时,这些标题会自动从工作表1中显示(使用宏).工作表2中有2个按钮.
When i open sheet2,these headings automatically appears from sheet1(using macro).There are 2 buttons in sheet2.
1.Display-display product details on matching Prdct Id entered by the user(that also done through macro)
2.Add- To add new product,user can enter Prdct Id , PrdctNme, Unitprice and it will be copied to sheet1 (through macro)
Sheet1还包含其他具有fromulas的列(我在示例中未显示),sheet1可以动态增长.所以我想要的是当用户输入 Prdct Id,PrdctNme,Unitprice
时, PrdctQty
应该自动出现在sheet2中(以及我暂时不包括的其他计算列)),然后我可以将新产品添加到sheet1
Sheet1 also contains other columns having fromulas(which i didnt show in the example)and sheet1 can grow dynamically.
So what i want is when user enters Prdct Id , PrdctNme, Unitprice
then PrdctQty
should automatically come in sheet2 (along with other calculated columns which i am not including for the time being) and after that i can add the new product to sheet1
我尝试了此代码(来自stackoverflow)
i tried this code (from stackoverflow)
Sub dural()
Dim r As Range, ady As String
For Each r In Sheets("Sheet1").Cells.SpecialCells(xlCellTypeFormulas)
ady = r.Address
r.Copy Sheets("Sheet2").Range(ady)
Next
结束子
但是我得到的是sheet2中sheet1的完整副本以及值.我只需要公式而不是值
but what i am getting is a whole copy of sheet1 in sheet2 along with values.What i need is only formulas not values
推荐答案
即使我不确定正确的方法,我也找到了解决方法.
I found a way even though i am not sure its the right way.
Sub dural()
Dim r As Range, ady,ady2 As String
For Each r In Sheets("Sheet1").Cells.SpecialCells(xlCellTypeFormulas)
ady = r.Address
ady2=r.formula
Sheets("Sheet2").Range(ady).formula=ady2
Next
对我有用
这篇关于如何仅从一个Excel工作表复制公式,该工作表可以使用宏动态增长到另一工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!