"Find and Replace" in Sheet names

Is there a way (with code perhaps) to search and replace sheet names?

For example, I have 20 sheet names with "Direct (2)" in the name, and
I'ld like to replace teh "Direct (2)" in the sheet name to "Net" in
each instance.

Thanks, Jim

Excel3321 (40)
1/24/2006 4:33:53 PM
excel 39879 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 52

One way:

    Public Sub Direct2ToNet()
        Const sRepl As String = "Direct (2)"
        Dim ws As Worksheet
        Dim nPos
        On Error Resume Next
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                 If .Name Like "*" & sRepl & "*" Then _
                    .Name = Replace(.Name, sRepl, "Net")
            End With
        Next ws
        On Error GoTo 0
    End Sub

In article <1138120433.680654.213520@g49g2000cwa.googlegroups.com>,
 "Excel@shoenfeltconsulting.com" <jimhome@starplace.com> wrote:

> Is there a way (with code perhaps) to search and replace sheet names?
> For example, I have 20 sheet names with "Direct (2)" in the name, and
> I'ld like to replace teh "Direct (2)" in the sheet name to "Net" in
> each instance.
> Thanks, Jim
jemcgimpsey (6723)
1/24/2006 5:09:32 PM
Example VBA Macro to play with.

Hope this helps


Sub Renamesheets()

Dim S As Worksheet

For Each S In ActiveWorkbook.Sheets
V = InStr(1, S.Name, "Direct", vbTextCompare)
If (V > 0) Then
S.Name = "Net " + Mid(S.Name, V + 7, Len(S.Name))

End If

End Sub

Excel@shoenfeltconsulting.com wrote:
> Is there a way (with code perhaps) to search and replace sheet names?
> For example, I have 20 sheet names with "Direct (2)" in the name, and
> I'ld like to replace teh "Direct (2)" in the sheet name to "Net" in
> each instance.
> Thanks, Jim
nospam630 (1)
1/24/2006 5:17:46 PM

Similar Artilces:

Find and Replace a color
I have been asked this by two different people in the past two weeks! Does anyone know how to quickly find all cells that are filled in yellow (for example) and change them all to purple (or white, or whatever)? Not conditional formats, but actual cell formats. This is driving me bonkers. something like this. Modify to suit. for each c in selection if c.interior.colorindex=3 then c.interior.colorindex=4 next -- Don Guillett SalesAid Software donaldb@281.com "Ronnie" <anonymous@discussions.microsoft.com> wrote in message news:883f01c49601$95dc1a20$a501280a@phx.gbl... ...

find and replace with zero as the first digit in a cell
I have a spreadsheet with part number, many of which begin with 0 and Excel automatically removed the 0. I want to use find and replace for blocks of this to convert it back but while it indicates it found and replaced X number of entries, there is no chage to the data. I've got the cells formatted as text and if I do the replacement one at a time by typing it seems to hold. Thanks George, Apparently your part numbers are numbers, not text. Excel stores the value of a number, not not its digits, and leading zeroes don't play a part. You can format it for leading zeroes (Forma...

Find and Replace 04-13-10
Good day all, I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre A couple of alternatives: - format the cell as Text before removing the dashes - put an apostrophe before the 0 before removing the dashes - remove the dashes in another cell, using SUBSTITUTE: =SUBSTITUTE(A1,"-","") - give the cell a custom number format like 000000000 Hope this helps, Hutch "Pierre" ...

nonbreaking hyphens
Hi, <br> I have created a document full of breaking hyphens which I really need to be nonbreaking hyphens. Is there any quick and easy way of changing them all at once, or do I need to go through one at a time? Find and replace doesn't seem to work, because I can't insert the character into the replace form. What version of Word? Click the triangle to expand the Replace dialog, and use the Special menu to insert the nonbreaking hyphen code. nanghelo@officeformac.com wrote: > Hi, > I have created a document full of breaking hyphens which I really need > to be nonbre...

Find and replace values across multiple fields
I would like to find and replace several values in my Access table.For example, I would like to change all "%40" to "@", but rather thanspecify a single field/column of my table to search through, I wouldlike the code to search through all of the fields in my table (thereare too many replacements and too many field names to specify).How can I modify the code below so that it searches through each fieldin the table?Public Function RunCleanValues() DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =Replace([fieldname],'%40','@');"End FunctionThank yo...

FIND and REPLACE characters needed
What characters (~, #, ?) do I use to Find and Replace item cells of first following row with second row below? FIND these types, eg: =(DSUM(DIVOH,$CX$1,$DG$2:$DG$20) Replace with these types, eg: =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) Thanks. Peter C Peter, In general, first find and replace = with '= Then F&R =DSUM with =IF(ISERROR(DSUM Then F&R ) with )),0) Then F&R '= with = Of course, =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0) isn't a good formula, so that isn't what you really want to do. Better would be a macro: Select all your cel...

Advanced find and replace?
Is there a way to Find and Replace a string by directing excel to a lookup table? I have a big spreadsheet with lots of text strings. I want to find all strings containing "abc", and replace it with "xyz". But I have about 40 such replacements to make, and was wondering if I could direct excel to a 2-column table, where it would see the 40 pairs of strings that I want it to find and replace. I am familiar with >Edit >Replace, but am hoping there is a way to automate and accelarate the task. Any ideas Dan With not too much error checking. Option Explicit Sub tes...

Excel 2003 Find and Replace
I have a column of numbers. Some numbers vary in the number of characters and some are strickly numbers; others are a mix of numbers and letters. I would like to replace those numbers with the same numbers but with parenthesis around each of them. How do I do that? Paste a representative sample of your data into a post so's we can have a look. Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 12:20:01 -0800, jr0410 <jr0410@discussions.microsoft.com> wrote: >I have a column of numbers. Some numbers vary in the number of characters >and some are strickly numbers; others a...

Find and Replace: Flashing Rectangles-- Word 2003
When I bring up the search and replace dialog box, I get quick flashing rectangular lines that flash only briefly. The same happens when I cancel the box. They come up and down from the bottom of the screen with the dialog box. Any idea on how to stop them?? I'm guessing at what you're seeing, but try this: Go to Tools > Options > General and uncheck "Provide feedback with animation". -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. ...

Find and Replace any digit
I am working in Word 2007. I am trying for a different find and replace option but I am not getting what I want. Here is the scenario: I have many documents in which the phone numbers are given as (000)-000-0000. I want all the numbers to replace with +1 prefixed and without brackets and dashes, like this, +1 000 000 0000. Now, I am able to find the numbers using ^# (any digit) in "Find What" by entering(^#^#^#)-^#^#^#-^#^#^#^#. But couldn't replace with the format I am looking for which is +1 ^#^#^# ^#^#^# ^#^#^#^# (+1 prefixed and no dashes). It is giving...

how to lock the find and replace dialog box in a corner
In Office 97 Excel, the find and replace dialog box could be moved out the field of view and it would stay there even afer to programe was closed. The next time it was turned on it was where it had been the last time. With office 2003 Excel, the box will be in the middlw of the screen. After it is moved, it will stay where it was moved as long as to program is open. After being closed and re-opened, the box will be back in the center of the screen. Is there a way to keep the box where it was moved to like it did in excel '97? ...

Find and Replace Permissions change on Public Folders
OK, I've been tasked with changing permissions on Hundreds of public folders. We are bringing up a thousand new security groups in a new naming convention, they are going to replace Mail enabled security groups currently assigned to public folders. How can I simplify this process? Is there a tool that performs a function like "subinACL" that will perform a Find/Replace on public folders? pfDavadmin might be able to help you out. It can export permissions and you can modify the permissions file and then import them back. "dnilson@gmail.com" wrote: > OK, I'...

Excel 2003 Find and Replace is changed to previovois ver.-awful!!
Well, the 2003 version of Excel has several changes compared to E2000 and older. As an example the find and replace function now has as default the active box "replace with", while earlier had "Find what". For no good reason one has to re learn. And this is just one change. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft We...

Find and Replace/ Excel to Word problem
I am working on a project that requires me to replace customers names with code numbers in a 80,000 page word document (I broke it into 2 45,000 page docs). The customer names with the corresponding number are in an excel document. For example it will say Smith in column A and the number that will replace him in column B. I want to know if their is a script, macro or some program that would allow me to have Excel and Word talk to each other so the name will appear in Word's Find field and the corresponding number will appear in the Word's 'Replace' field. Then it should run...

find and replace in workbooks
Is it possible to do a find and replace in multiple workbooks at once. If so how is it done. Thankyou for all you help. You would need VBA for this, to open each workbook, one at a time, do the "find and replace" and then save/close that workbook, open the next, etc etc............ Vaya con Dios, Chuck, CABGx3 "Glenn" wrote: > Is it possible to do a find and replace in multiple workbooks at once. If so > how is it done. Thankyou for all you help. ...

How do I find and replace "values" (like #N/A) in a worksheet?
When I use V-lookup, I often have numerous results that appear as "#N/A". I would like to be able to delete all of the "#N/A" values, or replace them with a 0, withour have to individually select and delete them. If you've already converted to values, then: select your range edit|Replace and replace all. If you want to adjust your =vlookup() formula: =if(iserror(vlookup()),"",vlookup()) or =if(iserror(vlookup()),0,vlookup()) hdc wrote: > > When I use V-lookup, I often have numerous results that appear as "#N/A". I > would like to ...

Keyboard Shortcut to toggle b/w Find and Replace and Active Worksheet?
Hi: I open the Find and replace Dialogue Box using CTRL+F and then enter the value I am looking for. Excel moves the cursor to the cell in question with the found value. Is there anyway to then use the keyboard to move from the dialogue box into the actual worksheet, without having to mouse click back into the open file? It seems there has to be some type of way to use the keyboard to move from the Find Dialogue box without using the mouse. I hope I am being clear on what I am trying to do, it seems very simple yet I cannot figure out what keyboard command to use to basically select the Exce...

Adding line breaks by using find and replace
I am trying to import data from a text file into an excel file. I want the address to be all in one field rather than each line being in a separate field. How can I replace the code ^l (or any other code) with a line break so that the address appears correctly in the Excel file? Soozie, Are you sure you want the address fields combined? I've had to mess around with more applications where that had been done, in order to separate the address components. Rather than working with codes, you might want to concatenate some cells with a formula: =A2 & " " B2 & " ...

Find and Replace #16
Hi all how do i find and replace multiple items, i know how to do one, for example find 2 replace with 3 But how do i find 2,4,5,6,7,8,9 replace with 3 all in the one go!!! thanks andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639 View this thread: http://www.excelforum.com/showthread.php?threadid=487793 Hi Koba, Only way I can think of is to run a macro like this. Sub ReplaceEm() Cells.Replace What:="2", Replacement:="3" Cells.Repla...

Help w/ Advanced Find and Replace
Hello all - I need your expertise. I have an Excel file with a couple of columns that I'd like to do a 'Find and Replace' on - where the find text is "***sig***" and the replace text is a multi-line email signature such as: Joe Smith ABC Realty Company (555) 555-1212 Of course, the normal find/replace function in Excel doesn't recognize multi-lines of data. I tried to use the normal function - I input the find string then pasted a one-line version of the signature and then hit 'Alt 010' for each new line started from the end (you can't s...

find and replace in excel
I want to find a way to replace automatically a single letter in a formula without replacing the similar other letters in the same formula. E.G. Sheet1!E45 change E45 to F45 without changing the other e's in the word sheet Thank you Nicos, one way replace E45 with F45 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Nicos" <Nicos@discussions.microsoft.com>...

find and replace contents of tables and text boxes
I have a 200 page document in publisher 2007. The contents of each page differ by only a small amount of text at the top of each page. Each page contains a large table and several text boxes. I need to replace the old information in the table and several text boxes with the same new information for all 200 pages. How do I do this quickly and efficiently? Put the table and text boxes on the master page. Ctrl+m. -- Mary Sauer http://msauer.mvps.org/ "Katy" <Katy@discussions.microsoft.com> wrote in message news:56F513B4-D272-4421-B9D6-979AF70D74A9@microsoft.com... >...

Excel Find and Replace
I am trying to edit documents that contain numerous strings of alpha-numeric characters, i.e. N1X3556558 N23G555681 N356M45465 What I need to do is remove the the first alpha character along with the following numerals up to the second alpha character. I have tried to use the text to columns feature but it doesn't work in this case because there is no set number of numerals following my first alpha character. Is there a way to do this in Excel? Hi! Maybe you can use this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MID(A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("2:&...

can i find and replace formula in a chart
i need to change the formulas in a number of charts from say january to february, can this be doen if so how? Jon Peltier has instructions for changing the series formulas in a chart: http://www.peltiertech.com/Excel/Charts/ChgSrsFmla.html At the bottom of the page, there's a sample file you can download, to make the process easier. timjimb wrote: > i need to change the formulas in a number of charts from say january to > february, can this be doen if so how? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Find and replace a set of strings in Powerpoint
Hi, I need to find a set of words in powerpoint and update them with another set of words. Though we have the "Find and Replace" option in powerpoint, changing numerous words is quite hectic. I saw the following code in the website pptfaqs.com. ----- Sub TheCatInTheHat() Dim oTextRange As TextRange Dim OriginalString As String Dim NewString As String Dim FindThis As String ' what string are we looking for? Dim SubstituteThat As String ' what do we replace it with FindThis = "XXXXXX" SubstituteThat = "yada yada yada" Set oTextRange = ...