"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

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

2 Replies
302 Views

Similar Articles

[PageSpeed] 31

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
0
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

Next
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
> 
0
nospam630 (1)
1/24/2006 5:17:46 PM
Reply:

Similar Artilces:

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...

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...

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'...

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 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 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...

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 = ...

find and replace categories and labels?
I'd like to find all appointments whose subject is X and with a category and label of <don't care>, and replace the category and label with specified values. I can find them ok, but I can't work out how to batch replace the categories or labels. eg if I have 100 appointments of subject "Call Mum", some with no category and others with category "Personal", some with no label and others with label "Phone Call", I want to make them all of category Personal and label Phone Call. Any help appreciated. thanks David When you sort by Category you ca...

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:&...

Excel find and replace issue #2
When I go to Edit - Find - find and replace - and have something copied I want to look for, When I click paste Nothing happens. This has only been going on for a few days. Have any ideas what got accidently (or updated) changed? Will it paste if you use the ctrl-v shortcut? buddyorliz wrote: > > When I go to Edit - Find - find and replace - > and have something copied I want to look for, > When I click paste Nothing happens. > This has only been going on for a few days. > Have any ideas what got accidently (or updated) changed? -- Dave Peterson YES it Will. So is it j...

Find and Replace (Colors)
If I find and replace (-) every cell that has (-) will convert back to their original color which I spent hours re-doing. Anyone know anything about this? Check your first post. Richard wrote: > > If I find and replace (-) every cell that has (-) will convert back to their > original color which I spent hours re-doing. Anyone know anything about > this? -- Dave Peterson Dear Richard, maybe this small add-in will help you: http://www.office-excel.com/excel-addins/find-and-replace.html Best regards, Evgeny "Richard" <miller6708@cox.net> wrote in message n...

find and replace issue in macro
Hello, I have a macro in Excel 2007 where I do a copy from one spreadsheet file and special paste to another sheet in another file. This works fine and it was created by recording a macro and then doing some minor modifications. I wanted to then do a find and replace where I make certain rows bold and a different color, so I recorded another macro to see what it did so I could insert the relevant code into my original macro. I did that but the results when using the macro change the size of the text, making it smaller. If I then go into format cells and the text tab everything looks fi...

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...

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...