Do you have a worksheet named "ThisSheet"? I think I'd do this

Dim myWS as Excel.Worksheet
Dim CopyFrom1 As Range

on error resume next
Set myWS = Worksheets("ThisSheet")
on error goto 0

if not myWS is nothing then
Set CopyFrom1 = myWS.Range("B2:B8")
end if

If it's the ActiveSheet, just use that.

HTH,
Barb Reinhardt


"David" wrote:

Hi Group,

Everyone having a good day? I am getting a type mismatch and can not figure
out why?

Dim CopyFrom1 As Range
Set CopyFrom1 = Worksheets(ThisSheet).Range("B2:B8")

Thanks for your help.

David