'Sub Lakeland_ACK2()
Sub Lakeland_ACK2(in_wb)

Dim wb  As Workbook
 Set wb = Workbooks.Open(in_wb)

'Declare Variables
 Dim TSheet As Worksheet         'Template Tab
 Dim DSheet As Worksheet         'Data Tab
 Dim ClientName As String
 Dim StartDate As String
 Dim StopDate As String
 
'Declare variable to represent HEX color
 Dim MyHexColor As String

'Assign HEX color to the MyHexColor variable
 MyHexColor = "#0097E6"  'Vibrant Blue
    
'Set Variables
 Set DSheet = Worksheets("Raw Data")
 Set TSheet = Worksheets("WHITE PLAINS")
 
 ClientName = DSheet.Range("E2").Value
 StartDate = DSheet.Range("F2").Value
 StopDate = DSheet.Range("G2").Value
 
'Make Sheet1 Active Sheet
Sheets(1).Select

'Set column widths
TSheet.Columns("A").ColumnWidth = 35.22
TSheet.Columns("B").ColumnWidth = 30.33
TSheet.Columns("C").ColumnWidth = 15.22
TSheet.Columns("D").ColumnWidth = 17.22

'Set Title Values
 TSheet.Range("B1:D1").Replace What:="(Client/Hospital Name)", Replacement:="(Client - " & ClientName & ")"
 TSheet.Range("B1:D1").Replace What:="Placement Dates from Monday, June 26, 2023", Replacement:=StartDate
 TSheet.Range("B1:D1").Replace What:="through Sunday, July 2, 2023", Replacement:=StopDate
 
'Remove Columns used for Veriable(s)
'DSheet.Columns("E:G").Delete
 

'Set Header format
 TSheet.UsedRange.Rows(2).Font.Bold = True
 TSheet.UsedRange.Rows(2).Font.Bold = True
 TSheet.UsedRange.Rows(2).Font.Size = 11
 TSheet.UsedRange.Rows(2).Font.Color = vbWhite ' white
 TSheet.UsedRange.Rows(2).Interior.Color = Application.Hex2Dec(Mid(MyHexColor, 6, 2) & Mid(MyHexColor, 4, 2) & Mid(MyHexColor, 2, 2)) 'Vibrant  blue

'Freeze Top Row
With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 2
    .FreezePanes = True
End With
    
'Rename Template Sheet
Sheets(1).Name = ClientName

'Delete Sheet
' Application.DisplayAlerts = False
' DSheet.Delete
' Application.DisplayAlerts = True
 
'Put cursor in cell A3
Sheets(1).Range("A3").Select
    
wb.Close SaveChanges:=True

End Sub