VBA Excel 2010 - Changing Multiple Sheet Names

**d3adtreebark** · 12-22-2014, 06:05 PM

After many attempts to alter a sample i stumbled across, "A1 change all sheet names - sample", and many hours of frustrating research; I cannot seem to find any solution which brings me here in hopes of some help on a VBA macro that will allow me to use the data from a specific cell(D3) on a different sheet (Sheet8) to change the name of the tab at the bottom of the screen. I will then take this formula and implement the remainder of the cells, is this the best way to do it?
Sheet 8 is my master sheet and will be hidden upon completion of the book. My ultimate goal is to have sheets 1,2,3,4,5,6, and 7 take the data from (in order) Cell D3,E3,F3,G3,H3,I3,J3 of which will then change the name of that sheets 1 - 7 to whatever data is in the cell (given that there are no illegal char's etc). Currently Sheet 1 is named Monday , sheet 2 is named Tuesday , etc.... I have already set up corresponding dates on sheet 8(my master sheet) to look like this " Monday - 5-Jan-15 ".

Is it possible to do this?

THANK YOU SO MUCH!!!!

**pjwhitfield** · 12-22-2014, 06:34 PM

Please note, this only works as long as the sheets were originally created in order eg Sheet 1 was originally called "Sheet1", Sheet 2 "Sheet2" etc

Sub renameSheets()
For xCol = 1 To 7
      newname = Cells(1, xCol + 3).Value
      Worksheets(xCol).Name = newname
Next xCol
End Sub

**pjwhitfield** · 12-22-2014, 06:37 PM

Actually, scratch that, just read your naming convention

Sub renameSheets()
sheetnames="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
oldnames = split(sheetnames,",")
For xCol = 1 To 7
      newname = Cells(1, xCol + 3).Value
      Worksheets(oldnames(xCol-1)).Name = newname
Next xCol
End Sub

will work

VBA Excel 2010 - Changing Multiple Sheet Names

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Excel 2010 - Changing Multiple Sheet Names

Re: VBA Excel 2010 - Changing Multiple Sheet Names

Re: VBA Excel 2010 - Changing Multiple Sheet Names

Thread Information

Users Browsing this Thread

Similar Threads

Changing code name of excel 2010 sheet using word 2010 vba

Charting - Changing Multiple Line Weights - Excel 2010

Excel 2010: Changing Source data on multiple pivot tables

excel, changing 2 sheet names by input on one tab?

Changing Sheet Names Based on # Values in Names

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions