Thread: VBA for excel to extract formula from another worksheet

hello

my request is a bit odd i admit
but i have to extract the actual formulas of an excel sheet

i understand and tried to look at these by using the built in functionality. but actually need to retrieve these formulas for reference in another piece of work.

i am no VBA porgrammer so my apologies for the lame code given here.

but i would really appreciate some assistance to make this work.

the rational for the code is:
loop over every cell (some are blank - but need to copy these too)
write a function (GetFormula) that extracts the formula from a cell in the other sheet - using same reference (A1 extracts from A1 , B1 from B1, and so on)
copy the output, and paste it back as text


it works, but VERY slow.

Many thanks for your suggestion guys.

Sub copyingFormulas()

' try to speed things up
Application.ScreenUpdating = False

Dim currFormulaTxt As String, dtFormat As String
Dim startTm As Date, endTm As Date
Dim currLoopRng As String, maxRow As Integer, maxCol As Integer

Sheets("getF").Select
maxRow = 21
maxCol = 83

startTm = Now()
For r = 1 To maxRow
For c = 1 To maxCol

Cells(r, c).Select

Cells(r, c).Value = "=GetFormula( 'baseline'!RC )"
Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next c
Next r

endTm = Now()

dtFormat = "yyyy/MM/dd hh:mm:ss"

Sheets("getF_log").Select

Cells(1, 1).Select

Cells(1, 1).Value = Format(startTm, dtFormat)

Cells(2, 1).Select

Cells(2, 1).Value = Format(endTm, dtFormat)

End Sub


Function GetFormula(rng As Range) As String

    GetFormula = rng.Formula

End Function

Why dont you use pastespecial formulae first and then pastespecial values instead of using GetFormula?

thanks

but if i select cells from the sheet that i need the formulas from then go to a blank sheet and choose paste special - i get the option for:
formulas - pastes the formula which are executed
values - the values of the calculations

but none gives me the option to have the formula pasted as a text

for instance if i have a cell with a formula like:
=sum(a1:d1)

i need to have the TEXT as:
=sum(a1:d1)

instead of whatever the output

does that clarify - or did i miss this option that you mentioned?

thanks,

sorry

but is this function that you mentioned

Cells.SpecialCells(xlCellTypeConstants, 3).PasteSpecial Paste:=xlPasteFormulas

found here: http://www.mrexcel.com/forum/showthread.php?t=330643

i am not sure how to use it?
could you please explain me that?