CODEWITHSUNDEEP
excelvba
joselyn reda
joselyn reda

Reputation: 17

Update "Replace" Macro

The below macro removes a forward slash with an empty space. For example, I needed this brand "RE/DONE" to appear as "RE DONE" since excel doesn't allow certain characters when you are naming the sheet tabs. I would like to update the below macro with if it also sees a colon (:) to also replace with an empty space. I tried to add it replace it with:

strPI = Replace(Left(pi.Name & " " & ws.Range("P1"), 31), "/ & :", " ")"

but it debugged. Can anyone help? I have a brand called "N:Philsophy" and need it to appear as "N Philsophy". I want make sure the macro knows if it sees a forward slash or a colon to replace it with an empty space.

  strPI = Replace(Left(pi.Name & " " & ws.Range("P1"), 31), "/", " ")
  On Error Resume Next
    Sheets(strPI).Delete
    Sheets(strPI1).Delete
  On Error GoTo 0
  ws.Copy After:=Sheets(Sheets.Count)
  With ActiveSheet
    .Name = strPI
    With .PivotTables(1).PivotFields(strPF)
      .PivotItems(pi.Name).Visible = True
      .CurrentPage = pi.Name
    End With
  End With

Upvotes: 0

Views: 44

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

wrap one Replace in another:

strPI = Replace(Replace(Left(pi.Name & " " & ws.Range("P1"), 31), "/", " "),":", " ")

Upvotes: 2

Related Questions