Results 1 to 3 of 3
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I have a workbook that ultimately includes separate worksheets for each of 13 weeks. I start with a sheet for the first week, then at the appropriate time I create a sheet for the second week, and so on. I have good reasons for doing it this way, as opposed to creating the 13 weeks all at once.

    The workbook contains a generic 'NewWeek' sheet which I copy and rename 'wk1' for the first week. I update 'wk1' through the week. When the week is over, I copy 'NewWeek' and name the new sheet 'wk2'. This continues week after week until I copy 'NewWeek' into 'wk13'. When that week is over, I start a new workbook and repeat the process for the next 13 weeks.

    I want to use a macro to automate the copying of 'NewWeek' into the weekly sheets. To start the automation, I've put a macro with a command button onto 'NewWeek'. The button and macro get copied to each weekly sheet as it is created. It's a big step in the right direction. When a week is finished, I just click the command button on its sheet, and a new sheet, named 'NewWeek (2)' is created in the proper place in the workbook.

    The next step, if it can be done, is to automate the naming of the new sheet. If I click the command button on sheet 'wk1', I'd like the resulting new sheet to be named 'wk2'. Ditto 'wk2' to 'wk3', etc. I'm thinking that the new name might be somehow gotten by incrementing 'wkN' to 'wkN+1', or by using some data that I could put into each sheet. (Of course I can name the new sheet manually, but that seems so crude, given the wonderful power of Excel!)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Assuming the original book is only one sheet:
    Assign the following to your button.

    Code:
    Sub btnNewWeek_Click()
        Sheets("NewWeek").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Wk" & Format(ActiveSheet.Index - 1, "00")
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a variation - It allows for nonWk sheets existing in the same workbook.

    Code:
    Sub btnNewWeek_Click()
    Dim i As Integer, iLstWeek As Integer
        
    For i = 1 To Sheets.Count
        If Left(Sheets(i).Name, 2) = "Wk" Then
            If Val(Mid(Sheets(i).Name, 3)) > iLstWeek Then
                iLstWeek = Val(Mid(Sheets(i).Name, 3))
            End If
        End If
    Next
    
    'add sheet and rename
       Sheets("NewWeek").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Wk" & Format(iLstWeek + 1, "00")
    End Sub

Posting Permissions

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