Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing values on multiple sheets using a macro
Hi,
Here's what I need to do: select all sheets in a workbook and do a replace of "\=" with "=" on each of these sheets. I can do it manually, but I have no exposure to VBA and am having troubles coding it. To do it manually- right click on the name of the worksheet - a menu will pop up. Click on "Select Al Sheets". Now, when you select "Find" or "Replace" from the Edit menu in Excel, all worksheets in your workbook will be searched. Any help would be really appreciated!!! Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing values on multiple sheets using a macro
Give this a try...
Sub DoStuff() Dim wks As Worksheet For Each wks In Worksheets wks.Cells.Replace "\=", "=" Next wks End Sub -- HTH... Jim Thomlinson "psoftguy" wrote: Hi, Here's what I need to do: select all sheets in a workbook and do a replace of "\=" with "=" on each of these sheets. I can do it manually, but I have no exposure to VBA and am having troubles coding it. To do it manually- right click on the name of the worksheet - a menu will pop up. Click on "Select Al Sheets". Now, when you select "Find" or "Replace" from the Edit menu in Excel, all worksheets in your workbook will be searched. Any help would be really appreciated!!! Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing values on multiple sheets using a macro
Awesome! It works! Thanks a lot, Jim!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing values on multiple sheets using a macro
This little bit of code should help you, but you'll have a little bit
of customizing to do before it'll probably work on your computer. Aside from placing the code into a macro, the only thing you will have to do is replace the sheet names ("Sheet1", "Sheet 2", etc...) with the actual name of your sheets. It works best if you double click on the sheet name, then copy and paste it into this, because sometimes there is a space that's not visible in the sheet name that will prevent the code from running. You can put as many sheet names as you like as long as they are in your workbook, just make sure to put quotes around them and separate them with commas. Good luck. Sub SearchReplaceWorkbook() Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet1").Activate Cells.REplace What:="\=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing cell reference on multiple sheets in one go.... | Excel Worksheet Functions | |||
Help needed replacing multiple cells from a list of values. | Excel Discussion (Misc queries) | |||
Lookup multiple values on multiple sheets | Excel Programming | |||
Filtering and replacing values through a macro | Excel Worksheet Functions | |||
Replacing (identical) values in multiple workbooks simultaneously? | Excel Programming |