VBA Macro: Copy and Paste Specific Row Range based on the Specific Value in a Cell.

This one macro will do it on any sheet that is active when you run the macro. I've changed the name of your main sheet from Sheet1 to Availability. I also tweaked your headers slightly for appearance.

Option Explicit

Sub DailyAvailability()
Dim wsMAIN As Worksheet, wsME As Worksheet, meFIND As Range, LR As Long

Set wsMAIN = Sheets("Availability")             'the sheet with the hours
Set wsME = ActiveSheet                          'the currrent sheet to update

On Error Resume Next
With wsMAIN
    LR = .Range("A" & .Rows.Count).End(xlUp).Row        'find the last row of availability date
    Set meFIND = .Rows(2).Find(wsME.Name, LookIn:=xlValues, LookAt:=xlWhole)    'find the current day
    If Not meFIND Is Nothing Then                       'if sheetname/day is found, proceed
        wsME.UsedRange.Clear                            'clear prior info
        .AutoFilterMode = False                         'remove prior filters
        .Rows(3).AutoFilter meFIND.Column, "Active"     'filter daily column for "active" only
        .Range("A2:F" & LR).Copy wsME.Range("A2")       'copy name info, then copy hours
        .Range(.Cells(2, meFIND.Column - 2), .Cells(LR, meFIND.Column - 1)).Copy wsME.Range("G2")
        .AutoFilterMode = False
        Application.CutCopyMode = False
        wsME.Columns.AutoFit
    End If
End With

End Sub