Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch
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 "Barb Reinhardt" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch
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 wrote: 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 "Barb Reinhardt" wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch
Dave,
Thank you. This did it and I did gain some understanding. I was not in another app, just Excel. Thanks Again, David "Dave Peterson" wrote: 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 wrote: 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 "Barb Reinhardt" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch | Excel Worksheet Functions | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Type Mismatch | Excel Programming | |||
Type mismatch using rnge as Range with Type 8 Input Box | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming |