Rename Sheets based on date range
Hi All,
I would really appreciate some help with the following: The first sheet in my workbook is called "Control". The second sheet is called "A" The last sheet is called "B" In between sheets "A" and "B" are additional worksheets, one for everyday of a month for example May 1 through to May 31. On the "Control" worksheet I have starting in cell A2 the date May 1 followed by May 2 etc. What I need is some VBA code that will allow me to rename all the worksheets that are in-between the "A" and "B" worksheets with the list of dates that appear on the "Control" worksheet. In essence as the user enters a new date for example June 1 in A2 on the Control worksheet and drags the date down the column until it reaches June 30, all the sheets between "A" and "B" need to be updated. Any ideas or suggestions on how to accomplish the above? Thanks, Steve |
Rename Sheets based on date range
Steve,
You don't have to drag the names down, all you need is a start date in A2 Sub RenameSheets() Dim i As Long Dim sh As Worksheet Dim dte As Date Dim dte1 As Date If IsDate(Worksheets("Control").Range("A2").Value) Then dte1 = CDate(Worksheets("Control").Range("A2").Value) dte = dte1 For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control" And sh.Name < "A" And _ sh.Name < "B" Then If Month(dte) = Month(dte1) Then sh.Name = Format(dte, "mmm d") sh.Visible = xlSheetVisible dte = dte + 1 Else sh.Visible = xlSheetHidden End If End If Next sh Else MsgBox "Start position invalid" End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message news:%5jaf.389372$oW2.362335@pd7tw1no... Hi All, I would really appreciate some help with the following: The first sheet in my workbook is called "Control". The second sheet is called "A" The last sheet is called "B" In between sheets "A" and "B" are additional worksheets, one for everyday of a month for example May 1 through to May 31. On the "Control" worksheet I have starting in cell A2 the date May 1 followed by May 2 etc. What I need is some VBA code that will allow me to rename all the worksheets that are in-between the "A" and "B" worksheets with the list of dates that appear on the "Control" worksheet. In essence as the user enters a new date for example June 1 in A2 on the Control worksheet and drags the date down the column until it reaches June 30, all the sheets between "A" and "B" need to be updated. Any ideas or suggestions on how to accomplish the above? Thanks, Steve |
Rename Sheets based on date range
Wow Bob, the code works great!
Thanks again for your help. Cheers, Steve *** Sent via Developersdex http://www.developersdex.com *** |
All times are GMT +1. The time now is 07:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com