+ Reply to Thread
Results 1 to 7 of 7

[SOLVED] Replace Portion of Sheet Name based on Cell Value

  1. #1
    Forum Contributor
    Join Date
    10-27-2005
    Posts
    177

    [SOLVED] Replace Portion of Sheet Name based on Cell Value

    Hello everyone

    Please see attached.

    There are a number of sheets that start with P**, where ** is a number that changes. For example, today it is 01, tomorrow it will be 02, then 03, all the way to 12

    I would like a macro that finds the sheets whose name starts with P** and changes the ** to a value in cell A1 of Sheet5.

    Would appreciate any assistance on this.
    Attached Files Attached Files
    Last edited by uberathlete; 05-26-2016 at 02:17 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace Portion of Sheet Name based on Cell Value

    Hi,

    I don't understand. Which of the P** sheets should be changed to the A1 value. You can only change one sheet since sheet names must be unique.

    And before going too far with these 12 sheets may I ask what you use them for? If there's a requirement further down the line to summarise or analyse them in some way then you are storing up potential problems. If all the sheets carry essentially the same information differentiated only by being a different month/day/department/...whatever then seriously consider keeping all the data on a single sheet in a normalised 2 dimensional table.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    10-27-2005
    Posts
    177

    Re: Replace Portion of Sheet Name based on Cell Value

    Hi Richard. In this case, all the P** sheets should be changed.

    So say I enter 02 in cell A1 of Sheet5. When the macro is run, the sheet names should change:

    P01_1 becomes P02_1
    P01_2 becomes P02_2
    P01_3 becomes P02_3

    The sheets all carry different data so they cannot be combined into one sheet.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace Portion of Sheet Name based on Cell Value

    Hi,

    Here's one macro
    Please Login or Register  to view this content.
    I realise the sheets almost certainly carry different data but are the sheet column layouts the same and is there a reason why the rows could not be stacked underneath each other, with the addition of another column to hold the P01_1, P01_2...references.

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Replace Portion of Sheet Name based on Cell Value

    Maybe:

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    10-27-2005
    Posts
    177

    Re: Replace Portion of Sheet Name based on Cell Value

    Thank you so much Richard! This does exactly what I need!

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace Portion of Sheet Name based on Cell Value

    Quote Originally Posted by uberathlete View Post
    Thank you so much Richard! This does exactly what I need!
    My pleasure.

    And to both you and John thanks for the reps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copying a portion of a row based on cell criteria to another sheet
    By lucky.vjn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-20-2015, 04:05 PM
  2. Formula to Replace Portion of Cell that Contains Matched Text to Cell
    By celestealexandra in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2014, 03:56 PM
  3. Replace a portion of Multiple cell labels/designators
    By rhartmanUSC in forum Excel General
    Replies: 8
    Last Post: 03-03-2014, 07:10 PM
  4. VBA lock a portion of the worksheet based on cell value
    By hinklejt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2014, 04:23 PM
  5. Replace portion of data in 1 cell, with all contents of different cell & save as CSV?
    By 1lowbowtie in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2011, 11:51 PM
  6. cut a portion of a cell's contents and paste the cut portion into the net column
    By NJEvan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-12-2010, 05:08 AM
  7. Find & Replace only a portion of a cell
    By wewolfe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2007, 06:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1