Hi
Code:
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
Bookmarks