Results 1 to 3 of 3

extrapolate year from date

This is a discussion on extrapolate year from date within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet with columns 6/30/2010, 6/30/2011, 6/30/2012 etc. I want to create new sheets within the same workbook ...

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    25

    Default extrapolate year from date

    I have a spreadsheet with columns 6/30/2010, 6/30/2011, 6/30/2012 etc.

    I want to create new sheets within the same workbook with only the year as the label, so the resulting sheet labels will be 2010,2011,2012.

    I don't want to hard code the year as the label, because the dates may change. I just want to be able to reference the year and use that as the label. Below is the code I have right now, but it labels the tabs "Jun-10", "Jun-11" etc.


    HTML Code:
    Public Sub add_four_sheets()
    Dim name1, name2, name3, name4 As String
    Dim StrSrcSheet As String
     
    StrSrcSheet = "Maintenance"
    ' add and rename four sheets
        name1 = Worksheets("Maintenance").Range("Y6").Text
        name2 = Worksheets("Maintenance").Range("AL6").Text
        name3 = Worksheets("Maintenance").Range("AY6").Text
        name4 = Worksheets("Maintenance").Range("BL6").Text
     
     'Add FYE10
        Worksheets.Add After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = name1
        Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy Worksheets(name1).Range("A1")
        Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy
            Worksheets(name1).Range("A1").PasteSpecial xlPasteColumnWidths
     
    'Add FYE11
        Worksheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = name2
            Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy Worksheets(name2).Range("A1")
            Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy
            Worksheets(name2).Range("A1").PasteSpecial xlPasteColumnWidths
            
    'Add FYE12
         Worksheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = name3
            Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy Worksheets(name3).Range("A1")
            Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy
            Worksheets(name3).Range("A1").PasteSpecial xlPasteColumnWidths
    
    'Add FYE13
         Worksheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = name4
            Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy Worksheets(name4).Range("A1")
            Worksheets(StrSrcSheet).Range("A1:A6").EntireRow.Copy
            Worksheets(name4).Range("A1").PasteSpecial xlPasteColumnWidths
    End Sub()

  2. #2
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,539

    Default Re: extrapolate year from date

    Does this work for you? It assumes the values in those four cells are Excel dates.
    Code:
    Public Sub AddFourSheets()
        Dim wks         As Worksheet
        Dim vsRng       As Variant
     
        Set wks = Worksheets("Maintenance")
     
        For Each vsRng In Array("Y6", "AL6", "AY6", "BL6")
            Worksheets.Add After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = Format(wks.Range(vsRng).Value, "yyyy")
            wks.Rows("1:6").Copy
            ActiveSheet.Range("A1").PasteSpecial
            ActiveSheet.Range("A1").PasteSpecial xlPasteColumnWidths
        Next vsRng
    End Sub

  3. #3
    New Member
    Join Date
    Sep 2010
    Posts
    25

    Default Re: extrapolate year from date

    Worked like a charm! Thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com