How to copy only formulas from one excel sheet which can dynamically grow to another sheet using macro

1
votes

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

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 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

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

End Sub

but what i am getting is a whole copy of sheet1 in sheet2 along with values.What i need is only formulas not values

3
vbaexcelformulas

3 Answers

0
votes

Try Something like this : 

  Sub moveformulas ()

    Sheets(1).UsedRange.SpecialCells(xlCellTypeFormulas).Copy 
    Sheets(2).Range("A1").PasteSpecial

  End Sub
0
votes

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

it worked for me

0
votes
Sub CopyOnlyFormulas()

Sheets(1).UsedRange.Copy
Sheets(2).Cells.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone

    For Each cell In Sheets(2).UsedRange
        If Not cell.HasFormula Then
            cell.Clear
        End If
    Next

End Sub


Sub CopyDataAndFormulas()
Sheets(1).UsedRange.Copy
Sheets(2).Cells.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
End Sub