title: VBA - Change sheet reference in cell formula
body: <p>I am trying to create a code where I change all references to a specific sheet in a selected range to a new chosen sheet - i.e. the same relative cell reference in a different sheet. The sheet reference to be replaced (Original Sheet) and the replacement sheet reference (New Sheet) are chosen by the user via comboboxes in a userform. The code works fine as long as both the sheet names are comparable with regards to whether they need single quotation marks, i.e. if OSheet = "Sheet1" and NSheet = "Sheet2" or OSheet = "Sheet - 1" and NSheet = "Sheet - 2". However, I run into problems when they have different requirements for single quotation marks, e.g. OSheet = "Sheet1" and NSheet = "Sheet - 2", as the sheet reference in the formula would be Sheet1! vs 'Sheet - 2!'. </p>
  
  <p>Do any of you have any tips on how I can fix this problem and standardize this code?</p>
  
  <pre><code>Private Sub UserForm_Initialize()
  
  Me.OriginalSheet.Clear
  Me.NewSheet.Clear
  
  For i = 1 To Sheets.Count
      With Me.OriginalSheet
          .AddItem Sheets(i).Name
      End With
      With Me.NewSheet
          .AddItem Sheets(i).Name
      End With
  Next i
  
  With Me.OriginalSheet
      .ListIndex = 0
  End With
  
  With Me.NewSheet
      .ListIndex = 0
  End With
  
  End Sub  
  
  Private Sub CommandButton1_Click()
  Dim OriginalSheet, NewSheet As String
  Dim xRange As Range
  Set xRange = Selection
  
  OSheet = Me.OriginalSheet.Value
  NSheet = Me.NewSheet.Value
  
      With xRange
          .Replace What:=OSheet, Replacement:=NSheet, LookAt:=xlPart, _
                   SearchOrder:=xlByRows, MatchCase:=False, _
                   SearchFormat:=False, ReplaceFormat:=False
      End With
  
  Unload Me
  
  End Sub
  </code></pre>