excel - VBA to clean characters from CELL -

vba clean characters cell, script meant through worksheets in open work book , remove special characters (so can rename tab cell) cleaning page script run.

sub cleanchar() dim ws worksheet dim mystring string dim newstring string  each ws in worksheets     ws         mystring = range("a1").value         newstring = replace(replace(mystring, ":", ""), """", "")         range("a1").offset().value = newstring     end next ws  end sub 

i think issue might lacking . in front of range objects. state with ws don't use ws, range object referring 1 worksheet.

try modified code:

sub cleanchar()      dim ws worksheet     dim mystring string     dim newstring string      each ws in worksheets         ws             mystring = .range("a1").value             newstring = replace(replace(mystring, ":", ""), """", "")             .range("a1").offset().value = newstring         end     next ws  end sub