Create n number of tables based on number of days in that week

[Ashley91]

Hello I am Ashley. I am trying to learn VBA and this is my first project.


I am stuck at the last part.


So what I am trying to do is create a new workbook for the current month with separate sheets for each week. Each sheet should contain n number of tables where n is based on the number of days that week has. At the same time, I need to input the date on cell C2.


I attached a copy of my workbook here.


I appreciate all of your help. Thank you in advance.

[p45cal]

This should set you on the right track:
Sub CreateIntervalFile()
Dim botSh As Worksheet, dataSh As Worksheet, TempSht As Worksheet, Newsht As Worksheet
Dim NewWB As Workbook, TableHeaders, intCount As Long
Dim rng As Range, rng1 As Range, cll As Range
Dim CurrentSheet As String, strFilename As String

Set botSh = Worksheets("BOT")
Set dataSh = Worksheets("data")

Set rng = dataSh.Range("H1").CurrentRegion
Set rng = Intersect(rng, rng.Offset(1)).Resize(, 1)
CurrentSheet = ""

Set NewWB = Workbooks.Add
'On Error Resume Next
With NewWB
Set TempSht = .Sheets(1)
TableHeaders = Array("MNL Time", "EST Time Zone", "Voice Notes", "Chat Notes", "SPPT Notes", "JSD Notes", "Call/Chat/AHT Drivers", "Optimized Breaks", "System Issue", "Agent w/Issue", "Count of Issue", "Planned/Unplanned Activity", "WFM Action Taken", "Challenges", "Recommendation", "EOD Notes")
' Set up a table to copy repeatedly:
With TempSht
.Range("B1:C1").Value = TableHeaders

With .Range("A1:C1")
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.349986266670736
.Interior.PatternTintAndShade = 0
.Font.Size = 9
.Font.Bold = True
.Font.Name = "Calibri"
.Columns("B:C").ColumnWidth = 15
End With
.Range("A2").FormulaR1C1 = "=TEXT(RC[2],""DDDD"")"
.Range("C2").NumberFormat = "d-Mmm"
.Columns("A:A").ColumnWidth = 10

With .Range("A2:C2")
.Interior.Pattern = xlSolid
.Range("A2:C2").Font.Size = 9
.Range("A2:C2").Font.Bold = True
.Range("A2:C2").Font.Name = "Calibri"
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 15592941
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
With .Range("B3:Q3")
.Value = TableHeaders
.Font.Bold = True
.Font.Size = 9
End With

With .Range("B3:P3")
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorAccent1
.Interior.TintAndShade = 0.799981688894314
.Interior.PatternTintAndShade = 0
End With
With .Range("Q3")
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 10498160
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
.Font.Color = vbWhite
End With
.Columns("D:Q").ColumnWidth = 16

.Range("B4").FormulaR1C1 = "12:00 PM"
.Range("B5:B99").FormulaR1C1 = "=R[-1]C+TIME(0,15,0)"
.Range("C4").FormulaR1C1 = "=RC[-1]+TIME(12,0,0)"
.Range("C5:C99").FormulaR1C1 = "=R[-1]C+TIME(0,15,0)"

.Range("B4:C99").Value = .Range("B4:C99").Value

With .Range("B3:Q99")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True

.Borders.LineStyle = xlContinuous
.Borders.Color = RGB(0, 0, 0)
.Borders.TintAndShade = 0
.Borders.Weight = xlThin
End With
Set rng1 = .Range("A1:Q99").CurrentRegion 'rng1 is the table that's going to be copied repeatedly.
End With
'end of template table.

For Each cll In rng.Cells
If cll.Value <> CurrentSheet Then
Set Newsht = .Sheets.Add(After:=Sheets(Sheets.Count))
' On Error Resume Next
Newsht.Name = "15mins Inter - " & cll.Value
CurrentSheet = cll.Value
ActiveWindow.DisplayGridlines = False
ActiveWindow.Zoom = 94
' This routing will copy rows based on the quantity to a new sheet.
' Set this for the range where the Quantity column exists. This works only if there are no empty cells
With cll.Offset(, 1)
If IsNumeric(.Value) Then
' Check if the number is greater than 0
If .Value > 0 Then
' Copy this row as many times as .value
For intCount = 1 To .Value
' Copy the row into the next emtpy row in sheet2
rng1.Copy Destination:=Newsht.Range("B" & Rows.Count).End(xlUp).Offset(2, -1) 'added the -1 ' The above line finds the next empty row.
Next 'intCount
End If
End If
End With 'cll.offset(,1)
Newsht.Columns.AutoFit
End If
Next cll

Application.DisplayAlerts = False
TempSht.Delete
Application.DisplayAlerts = True
strFilename = ThisWorkbook.Path & "\" & "Tracker_" & dataSh.Range("C1").Value & ".xlsx"
.SaveAs Filename:=strFilename ', FileFormat:=75
End With 'NewWB
End Sub

[arnelgp]

that is not so Optimal, using many sheets.
you can have it in a Single sheet and just add a Column for month/year and another column for the week number.
you can then Filter it my month/year and week number.

also this is portable in MS Access table and will result in Normalized table.

[Ashley91]

p45cal - Thank you so much! You helped us save so much time! Now I will just have to figure out how to put the dates :)

arnelgp - Appreciate the input, however that's the clients' requirements, can't really argue with them :)

[arnelgp]

ganun ba?

[arnelgp]

i attempted to create but this time with Date.
also it is based on a Template (tplate.xlsx)

copy both workbook on same folder, run tracker and supply first
the month to create in B1.

sorry can't upload here, my browser won't allow me.
https://www.dropbox.com/s/ck0d7j82cnh6k13/tracker.zip?dl=0

[Ashley91]

i attempted to create but this time with Date.
also it is based on a Template (tplate.xlsx)

copy both workbook on same folder, run tracker and supply first
the month to create in B1.

sorry can't upload here, my browser won't allow me.
https://www.dropbox.com/s/ck0d7j82cnh6k13/tracker.zip?dl=0


Okay this is wayyy better than what I was doing, but the sheets need to start on a Monday except when the first day of the month doesn't fall on a Monday

[Ashley91]

i attempted to create but this time with Date.
also it is based on a Template (tplate.xlsx)

copy both workbook on same folder, run tracker and supply first
the month to create in B1.

sorry can't upload here, my browser won't allow me.
https://www.dropbox.com/s/ck0d7j82cnh6k13/tracker.zip?dl=0


Okay I was able to make all the sheets start on a Monday.

I will have the clients choose between the two. Thank you, p45cal / arnelgp

[arnelgp]

Okay I was able to make all the sheets start on a Monday.

I will have the clients choose between the two. Thank you, p45cal / arnelgp
:hi::friends:

[p45cal]

Who is/are the clients?

[Ashley91]

Who is/are the clients?

Hello, hmm not sure how to explain this but let me try :D
In a call center environment, we have what we call the "operations" aka the agents, team leads, operations manager(s) basically the front liners or those who talk to people calling that company for support. Then there's also us, the workforce people - the department that supports the operations in terms of managing the agents' schedules as well as their breaks. We provide the operations with reports (frequency and types based on whatever the operations require). So in this case, the operations people require that the tracker to have separate sheets based on the week.