Excel VBA: Copy paste a lrage range and remove ' in front in each cell

Dmitriy Kritskiy
Dmitriy Kritskiy used Ask the Experts™
on
I have a simple routine that copies and pastes formulas from one sheet to another when user clicks a button, let's say the following is the code:
ThisWorkbook.Worksheets("formulasheet").Range("A1:D500").Copy
    ThisWorkbook.ActiveSheet.Range("A1:D500").PasteSpecial xlPasteFormulas
    ThisWorkbook.ActiveSheet.Range("A1:D500").PasteSpecial xlPasteFormats

Open in new window


If I wanted to store there formulas as text so that they are not calculating when not needed and have '= in front of all of them on formulasheet tab, is there a way to write a code so that it removes ' when pasting values so that formulas become formulas again?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

If I understand your requirement correctly, I think the following routine will be what you are looking for:

Option Explicit
Public Sub Q_28691571()

  Dim objCell                                           As Range
  
  ThisWorkbook.Worksheets("formulasheet").Range("A1:D500").Copy
  ThisWorkbook.ActiveSheet.Range("A1:D500").PasteSpecial xlPasteFormulas
  ThisWorkbook.ActiveSheet.Range("A1:D500").PasteSpecial xlPasteFormats
  
  If Not (ThisWorkbook.ActiveSheet.Range("A1:D500").SpecialCells(xlTextValues) Is Nothing) Then
     For Each objCell In ThisWorkbook.ActiveSheet.Range("A1:D500").SpecialCells(xlTextValues)
         objCell.Formula = "=" & Mid(objCell, 2)
     Next objCell
  End If

End Sub

Open in new window


I have attached a sample workbook (".xls") file to demonstrate the execution of the above Visual Basic for Applications code.
Q-28691571.xls
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial