Thread: Copy formulas with text to new sheet

Hey again!

Really loving this place.

I have a macro that extracts text from multiple worksheets and places it in a column along with the cell reference and worksheet name. I'd like to do the same thing but instead of text I'd like it to pull formulas with text only. Some of the formulas have more than one word associated with them.

EX:

=IF($B34="","Enter Part Number",IF($D34="","Enter OEM",IF($F34="","Enter Quantity",IF($G34="","Enter Published List Unit Price",IF(VLOOKUP($D34,LAN_Range,2,FALSE)="","Enter NE Discount",($G34-(VLOOKUP($D34,LAN_Range,2,FALSE)*$G34))*$F34)))))

Here's the current formula:

Public Sub texttonewsheet()
Dim n As Long, i As Long
Dim Rng As range
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
n = 1
For i = 2 To Sheets.Count
For Each Rng In Sheets(i).range("A1:CI200")
If Rng.Value <> "" Then
If Not Application.IsNumber(Rng) Then
Sheets(1).range("A" & n).Value = Rng.Value
Sheets(1).range("B" & n).Value = Rng.Address
Sheets(1).range("C" & n).Value = Sheets(i).Name
n = n + 1
End If
End If
Next Rng
Next i
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Any help would be appreciated...

Thanks!

Sar

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here