How To Copy Data By Worksheet Name

[Admin]

Hi

Option Explicit

Dim ShtMain     As Worksheet

Sub kTest()
    
    Set ShtMain = Worksheets("Main")
    
    With ShtMain
        If .Range("b7").Value = "All" Then
            CopyPaste Left$(.Range("b2").Value, 3) & "-" & .Range("d2").Value
        Else
            .Range("b8:b34").ClearContents
            .Range("d8:r34").ClearContents
        End If
    End With
    
End Sub

Sub CopyPaste(ByVal ShtName As String)
    
    Dim Sht As Worksheet
    
    On Error Resume Next
    Set Sht = Worksheets(ShtName)
    If Err.Number <> 0 Then
        MsgBox "Sheet '" & ShtName & "' could not be found.", vbCritical + vbOKOnly
        Exit Sub
    End If
    
    On Error GoTo 0
    
    With ShtMain
        .Range("b8:b34") = Sht.Range("b8:b34").Value2
        .Range("d8:r34") = Sht.Range("c8:q34").Value2
    End With
    
End Sub

and call the kTest macro