Type mismatch

[David]

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

 

[Barb Reinhardt]

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]

Hi Barb,

I am failing to see what I am doing wrong.

Dim ThisSheet As Excel.Worksheet
Set ThisSheet = Worksheets(ActiveSheet.Name)
(Idea is that no matter what sheet I am on it will get the right name)

Dim CopyFrom1 As Range
Set CopyFrom1 = ThisSheet.Name.Range("B2:B8")
(Invalid qualifier)

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

I need to capture the sheet name, as this will run on many different sheets,
as desired. The ending row 8 will change and I am trying to capture the row
as Activcell.Row, so this is simplied.

Thanks,
David

 

[Dave Peterson]

Are you controling excel from a different application (MSWord for example)?

If yes, then make sure you're using an excel range--and not an MSWord Range

Dim ThisSheet As Excel.Worksheet
Dim CopyFrom1 As Excel.Range

Set ThisSheet = ActiveSheet 'no need using worksheets(activesheet.name)
Set CopyFrom1 = ThisSheet.Range("B2:B8")

Or just:
set copyfrom1 = activesheet.range("B2:B8")

If you're running this code from an excel workbook's project, then you don't
need those "Excel." qualifiers (but they won't hurt).

And are you sure that the activesheet is a worksheet--not a chartsheet or a
macro sheet or a dialog sheet or ...

 

[David]

Dave,

Thank you. This did it and I did gain some understanding. I was not in
another app, just Excel.

Thanks Again,
David