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

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?
Dmitriy KritskiyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.