Each section has edits in the RESIZE commands, too. Be sure to read up on what that does.
Option Explicit
Sub ConsolidateSheets()
Dim ws As Worksheet, NR As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Evaluate("ISREF(Consolidated!A1)") Then Sheets("Consolidated").Delete
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Consolidated"
NR = 2
With ActiveSheet
.Range("A1:M1").Value = [{"Date", "Time","Offered", "Answered", "Answer Delay", "Avg Ans Delay", "Max. Answer Delay", "Ans After Threshold", "Abandoned", "Max. Aban'd Delay", "Aban After Threshold", "Ans Delay At Skillset", "% Service Level"}]
.Range("A2").Select
.Rows(1).Font.Bold = True
ActiveWindow.FreezePanes = True
For Each ws In Worksheets
If ws.Name Like "Sheet*" Then
If IsDate(ws.Range("B5")) Then
.Range("A" & NR).Resize(24, 13).Value = ws.Range("A6:M29").Value
.Range("B" & NR).Resize(24).Value = .Range("A" & NR).Resize(24).Value
.Range("A" & NR).Resize(24).Value = ws.Range("B5").Value
ElseIf IsDate(ws.Range("B9")) Then
.Range("A" & NR).Resize(24, 13).Value = ws.Range("A10:M33").Value
.Range("B" & NR).Resize(24).Value = .Range("A" & NR).Resize(24).Value
.Range("A" & NR).Resize(24).Value = ws.Range("B9").Value
ElseIf IsDate(ws.Range("A10")) Then
.Range("B" & NR).Resize(8, 12).Value = ws.Range("A2:L9").Value
.Range("A" & NR).Resize(8).Value = ws.Range("A10").Value
ElseIf IsDate(ws.Range("A30")) Then
.Range("B" & NR).Resize(28, 12).Value = ws.Range("A2:L29").Value
.Range("A" & NR).Resize(28).Value = ws.Range("A30").Value
ElseIf IsDate(ws.Range("A31")) Then
.Range("B" & NR).Resize(28, 12).Value = ws.Range("A3:L30").Value
.Range("A" & NR).Resize(28).Value = ws.Range("A31").Value
End If
NR = .Range("B" & .Rows.Count).End(xlUp).Row + 1
End If
Next ws
.Columns.AutoFit
.Range("B:B").NumberFormat = "h:mm am/pm"
.Range("E:G, J:J, L:L").NumberFormat = "h:mm:ss"
End With
Application.ScreenUpdating = True
End Sub
Bookmarks