1)Day Wise columns by Creating new Workbook

How this template is useful:

Create new workbook and assign dates based on our requirement

Mention date number in F4 column Ex: i want upto 5th, mention 5, it prints like 1,2,3,4,5

Mention month name in I4 column., which month we require

Code Explanation:

Private Sub CommandButton1_Click()
'define two variable to store values for Month and Date
Dim D As Integer, M As String
'Store Month name
M = Range("I4").Value
'Store Month dates upto
D = Range("F4").Value
'Define Object variable to create new workbook
Dim wkb As Workbook
'Assigning value to variable
Set wkb = Workbooks.Add
'Define Loop variable
Dim Z As Integer
wkb.Sheets("Sheet1").Activate
'Define Loop Range
For Z = 1 To D
wkb.Sheets("Sheet1").Cells(2, Z + 1) = M & " " & Z & "," & " " & "2015"
Next
'Fit the data into cell
wkb.ActiveSheet.Cells.EntireColumn.AutoFit
Renaming of active sheet
wkb.ActiveSheet.Name = Range("i4").Value & " month"
End Sub

2) NESTED LOOPS:Day wise - For each sheet

Copy the code:

Private Sub CommandButton1_Click()
'1)Define existing worksheets
Dim oldsh As Integer
'2)Assigning values as existing worksheets count to OLDSH variable
oldsh = ThisWorkbook.Sheets.Count
'3)Define variable to store number of worksheets we need to create in new workbook
Dim shcount As Integer
'4)Number of worksheets is based on names in COLUMN "A"., This is dynamic
shcount = Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count
'5)Assigning number of required sheets in new workbook, which is stored in variable
Application.SheetsInNewWorkbook = shcount
'6)Define object variable to create new workbook
Dim wkb As Workbook
'Assigning value to object variable., using ADD method
Set wkb = Workbooks.Add
'7)defining variables ---- this is very crucial step for any program
'====================================================
Dim i As Integer 'for assigning sheet names
'define variable and assigning value for: DATES
Dim d As Integer
d = Range("E3").Value
'8)define variable and assigning value for MONTH
Dim M As String
M = Range("F3").Value
'9)for sheet names., why defined as 2?
'as sheet names data started from 2nd row
Dim n As Long
n = 2:
'10)define variable to copy data
Dim b As Long
'define loop variable for Z
Dim Z As Integer
'11)define loop variable to copy data from "D" Column to First column in newly created workbook
Dim data As Long
data = Range(Range("D2"), Range("D2").End(xlDown)).Rows.Count
'12)loop-1====for assigning sheet names
'======================================
For i = 1 To shcount 'first sheet to last sheet
'13)loop2 - to print dates and its formatting alignment
For Z = 1 To d
wkb.Worksheets(i).Activate
wkb.Worksheets(i).Cells(1, Z + 1) = M & " " & Z & "," & " " & "2015"
wkb.Worksheets(i).Cells(1, Z + 1).Select
'14)alignment of dates row
With Selection
.Interior.ColorIndex = 1
.VerticalAlignment = xlBottom
.Font.Size = 18
.Font.ColorIndex = 6
.Font.Name = "High Tower Text"
.Borders(xlEdgeLeft).LineStyle = xlDouble
.Borders(xlEdgeLeft).ColorIndex = 3
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeTop).ColorIndex = 3
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).ColorIndex = 3
.Borders(xlEdgeRight).LineStyle = xlDouble
.Borders(xlEdgeRight).ColorIndex = 3
End With
Next 'end loop 2
'=============================
'15)assigning vlaues to range("A1")
wkb.Worksheets(i).Range("A1").Select
With Selection
.Value = "Student Name"
.Interior.ColorIndex = 1
.VerticalAlignment = xlBottom
.Font.Size = 18
.Font.ColorIndex = 6
.Font.Name = "High Tower Text"
.Borders(xlEdgeLeft).LineStyle = xlDouble
End With
b = 2
'16)loop3 for pasting required data in column "A"., this is dynamic
'==================================================
For o = 1 To data
wkb.Worksheets(i).Cells(b, 1).Value = Cells(b, 4).Value
wkb.Worksheets(i).Cells(b, 1).Select
With Selection
.Font.Size = 18
.VerticalAlignment = xlBottom
.Interior.ColorIndex = 5
.Font.ColorIndex = 2
.Borders(xlEdgeLeft).LineStyle = xlDouble
.Borders(xlEdgeLeft).ColorIndex = 2
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeTop).ColorIndex = 2
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).ColorIndex = 2
.Borders(xlEdgeRight).LineStyle = xlDouble
.Borders(xlEdgeRight).ColorIndex = 2
End With
b = b + 1
Next 'end loop3
wkb.Worksheets(i).Cells.EntireColumn.AutoFit
'17)selection of used range
wkb.Worksheets(i).UsedRange.Select
'Assiginging values to resize the selection of used range
rowd = Selection.Rows.Count - 2
cold = Selection.Columns.Count - 1
'18)Identification of numbers of rows to create borders
s = Selection.Offset(1, 1).Resize(rowd, cold).Rows.Count
Selection.Offset(1, 1).Resize(rowd, cold).Select
'19)loop 4:for assigining borders for rows
'==================================================
For rowdata = 1 To s
With Selection
.Rows(rowdata).Borders(xlEdgeBottom).LineStyle = xlDouble
.Rows(rowdata).Borders(xlEdgeBottom).ColorIndex = 26
End With
Next
With Selection
'.Interior.ColorIndex = 37
.HorizontalAlignment = xlLeft
.Font.Size = 18
.Font.Name = "High Tower Text"
.Font.ColorIndex = 3
.Cells.Borders(xlEdgeRight).LineStyle = xlDouble
.Cells.Borders(xlEdgeLeft).LineStyle = xlDouble
.Borders(xlEdgeLeft).ColorIndex = 3
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeTop).ColorIndex = 3
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).ColorIndex = 3
.Borders(xlEdgeRight).LineStyle = xlDouble
.Borders(xlEdgeRight).ColorIndex = 3
End With
'20)assiging sheet names
wkb.Worksheets(i).Name = Range("A" & n).Value
n = n + 1
Next 'end loop1
'21)Reassinging value to Excel applciation with OLDSH. This is important step.if we missed this one, going forward all the newly opend workbooks will open with sheets in newly created workbook.
Application.SheetsInNewWorkbook = oldsh
End Sub