Auto Date Columns relative to the Worksheet Name

[JMB_0159]

Hi, I am after some help in creating an XL (2013) file which will enable me to create a Workbook with Worksheet tab names of Months of the year, also it will add the dates for that particular Month to row 2 within this worksheet. The worksheet will have Personnel Names in column A and the date ranges for that month start from column C. If anyone can help out it would be appreciated. The aim is to have the Jobs / Job Numbers allocated to personnel on particular days therefore I can keep track also holidays, RDO's etc.

Thanks in anticipation
James

 

[Fluff]

Hi
Is this what you're after

Sub NewBk()

    Dim Cnt As Long
    Dim Shts As Long
    
Application.ScreenUpdating = False

    Shts = Application.SheetsInNewWorkbook

    Application.SheetsInNewWorkbook = 12
    Workbooks.Add
    Application.SheetsInNewWorkbook = Shts
    
    For Cnt = 1 To 12
        With Sheets(Cnt)
            .Range("C2").NumberFormat = "@"
            .Range("C2") = Format("1/" & Cnt & "/2017", "dd/mm/yyyy")
            .Name = Format(.Range("c2"), "MMM")
            Select Case Cnt
                Case 1, 3, 5, 7, 8, 10, 12
                    With .Range("D2").Resize(, 30)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
                Case 2
                    With .Range("D2").Resize(, 27)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
                Case 4, 6, 9, 11
                    With .Range("D2").Resize(, 29)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
            End Select
            .Rows(2).NumberFormat = "dd/mm/yyyy"
        End With
    Next Cnt

End Sub

 

[JMB_0159]

Thanks very much worked perfectly.
Just another question could this macro be modified to highlight weekends e.g. sat & sun columns light grey ?

 

[gaganexcel]

With minor modification to above code by Fluff .

Sub NewBk()


    Dim Cnt As Long
    Dim Shts As Long
    
Application.ScreenUpdating = False


    Shts = Application.SheetsInNewWorkbook


    Application.SheetsInNewWorkbook = 12
[B]    Set NewBk2 = Workbooks.Add[/B]
  
  
    For Cnt = 1 To 12
        
        With[B] NewBk2.[/B]Sheets(Cnt)
            .Range("C2").NumberFormat = "@"
            .Range("C2") = Format("1/" & Cnt & "/2017", "dd/mm/yyyy")
            .Name = Format(.Range("c2"), "MMM")
            Select Case Cnt
                Case 1, 3, 5, 7, 8, 10, 12
                    With .Range("D2").Resize(, 30)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
                Case 2
                    With .Range("D2").Resize(, 27)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
                Case 4, 6, 9, 11
                    With .Range("D2").Resize(, 29)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
            End Select
[B]            .Rows(2).NumberFormat = "dd/mm/yyyy"[/B]

[B]            .Range("c2").FormatConditions.Delete[/B]
[B]            .Range("c2").FormatConditions.Add Type:=xlExpression, Formula1:="=WEEKDAY(C2,2)>5"[/B]
[B]            .Range("c2").FormatConditions(1).Interior.ColorIndex = 15[/B]
[B]            .Range("c2").Copy[/B]
[B]            .Range(.Cells(2, 3), .Cells(2, 31)).PasteSpecial (xlPasteFormats)[/B]
        End With
         
        Next Cnt


    End Sub

 

[JMB_0159]

Excellent, I know I am stretching the friendship but would it be possible to code so that when I run the macro it will not create another workbook.

Once again thanks
James

 

[gaganexcel]

No worries . I hope this will work fine.

Sub SameBk()


    Dim Cnt As Long
    Dim Shts As Long
    
Application.ScreenUpdating = False


    With ThisWorkbook
    For Cnt = 1 To 12
        If Cnt > .Worksheets.Count Then .Worksheets.Add after:=Sheets(Sheets.Count)
        With Sheets(Cnt)
            .Range("C2").NumberFormat = "@"
            .Range("C2") = Format("1/" & Cnt & "/2017", "dd/mm/yyyy")
            .Name = Format(.Range("c2"), "MMM")
            Select Case Cnt
                Case 1, 3, 5, 7, 8, 10, 12
                    With .Range("D2").Resize(, 30)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
                Case 2
                    With .Range("D2").Resize(, 27)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
                Case 4, 6, 9, 11
                    With .Range("D2").Resize(, 29)
                        .Formula = "=rc[-1]+1"
                        .Value = .Value
                    End With
            End Select
            .Rows(2).NumberFormat = "dd/mm/yyyy"
          
            .Range("c2").FormatConditions.Delete
            .Range("c2").FormatConditions.Add Type:=xlExpression, Formula1:="=WEEKDAY(C2,2)>5"
            .Range("c2").FormatConditions(1).Interior.ColorIndex = 15
            .Range("c2").Copy
            ThisWorkbook.Worksheets(1).Range(ThisWorkbook.Worksheets(1).Cells(2, 1), ThisWorkbook.Worksheets(1).Cells(2, 31)).PasteSpecial
        End With
    Next Cnt
    End With
End Sub

 

[JMB_0159]

Excellent works like a treat. Thanks so much for your assistance.

Regards - James