Renaming a workbook and worksheet on clicking a button

Question

Please find attach sheet.

Demo.xlsm

I have a Demo sheet . In it i have "Main" workbook which contains a button as"Next Week" which when clicked will change the date in "abc" sheet cell C1 to next week date (i.e. from 14-June-2015 to selected date.)

I wanted the workbooks named "sheet1,sheet2... sheet7" to change name accordingly to what the date in respective c1,E1...O1

ie. c1 value date will be sheet1 workbook name ie sheet1 will rename to 14-Jun-15 E1 value date will be sheet1 workbook name ie sheet1 will rename to 15-Jun-15 G1 value date will be sheet1 workbook name ie sheet1 will rename to 16-Jun-15, etc..

Additional thing - Renaming the same File/excel sheet to "ABC June 14 - June 20" where ABC is fixed value and June 14 comes from C1 and June 20 comes from O1.

Answer 1

Loop through the sheets and rename, this code will go into your Command Button 2

Private Sub CommandButton2_Click()
    Dim sh As Worksheet
    Dim ws As Worksheet
    Dim rng As Range, x
    Set ws = Sheets("abc")
    Set rng = ws.Range("A1")

    x = 2
    rng.Offset(0, x) = rng.Offset(0, x) + 7

    For Each sh In Sheets
        If sh.Name <> "Main" Then
            If sh.Name <> "abc" Then
                sh.Name = Format(rng.Offset(0, x), "dd-mmm-yy")
                x = x + 2
            End If
        End If
    Next sh

End Sub