Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Renaming sheets (Excel 2002)

    I have a form in Excel that is filled out for every day of the year. I'd like a workbook with 365 sheets, each named for its corresponding date. I'm able to make the workbook with 365 sheets/forms by using copy and paste without too much trouble. Is there an easier way to rename the sheets rather than one by one? One more question. Can I add the values from cell A1 through all 365 layers without having to name each sheet in the formula? Thanks for the help

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming sheets (Excel 2002)

    The VBA procedure below should create the worksheets for you:

    <pre>Public Sub CreateWorkSheets()
    Dim I As Integer, datStart As Date
    Dim oWS As Worksheet
    I = 0
    datStart = DateValue("1/1/2003")
    Do While Year(datStart + I) = Year(datStart)
    Set oWS = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
    oWS.Name = Format(datStart + I, "mmm-dd")
    I = I + 1
    Loop
    End Sub
    </pre>



    You should be able to add A1 on all of those sheets with the formula:

    <pre>=SUM('Jan-01ec-31'!A1)
    </pre>

    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming sheets (Excel 2002)

    I think that your code is going to try to name all of the sheets with the same name.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Renaming sheets (Excel 2002)

    Yes, <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>, I left out one line. Thanks for pointing it out, I will go and correct it now.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Renaming sheets (Excel 2002)

    <P ID="edit" class=small>(Edited by HansV on 25-Feb-03 00:17. Inserted missing line in code (thanks to Legare))</P>You could create a short macro to rename the sheets (or even to create them). Assuming you have already created 365 worksheets (I hope you have enough RAM), you could do this:

    Sub RenameSheets()
    Dim i As Integer
    Dim d As Date
    d = DateSerial(2003, 1, 1)
    For i = 1 To 365
    ActiveWorkbook.Worksheets(i).Name = Format(d, "mmmm d")
    ' I originally forgot to include the next line - it is essential
    d = d + 1
    Next i
    End Sub

    You can set another date format if you like, but you can't have a slash / in a worksheet name.

    Assuming that your sheets are named January 1 through December 31, the following formula will sum A1 through all sheets:<pre>=SUM('January 1ecember 31'!A1)</pre>

    Note the single quotes surrounding the range of worksheets.

  6. #6
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming sheets (Excel 2002)

    Thanks for the response. Your point about available RAM is worth noting. I may have to approach this from a different direction. I also appreciate your attention to summing cells among sheets. I couldn't find an answer in the help files.

  7. #7
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming sheets (Excel 2002)

    Thanks for responding to my post. I'm afraid I don't have much experience with VBA in Excel. It's also nice to see a response to what was probably a pretty simple question about copying cells.

Posting Permissions

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