+ Reply to Thread
Results 1 to 2 of 2

Rename Tab based on cell value, but only use first 30 characters

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    Dallas, tx
    MS-Off Ver
    Office 2010
    Posts
    2

    Rename Tab based on cell value, but only use first 30 characters

    All,

    I have a workbook that has 100 worksheets in it, i want the worksheets to be renamed based on the value in cell b1 for each worksheet. However if there are invalid characters or the name is too long it gives an error. How I just reference the first 30 valid characters.

    Thanks again.

    Option Explicit

    Sub LoopThroughSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ws.Name = ws.Range("b1").Value

    Next ws
    End Sub

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Rename Tab based on cell value, but only use first 30 characters

    try this

    Please Login or Register  to view this content.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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