Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error - Copying Data between Workbooks
Can someone let me know why I get a type mismatch error when the
following code is ran? Sub CopyFromLSS() Dim MyDir As Range, lsFile As Range, csFile As Range Dim wsTo As Worksheet, wsFrom As Worksheet Set MyDir = Sheets("WB").Range("B2") 'C:\06\ Set csFile = Sheets("WB").Range("B3") 'CS.xls Set lsFile = Sheets("WB").Range("B4") 'LS.xls Set wsFrom = Workbooks(lsFile).Worksheets("Sheet1") 'Why is there a type mismatch error? Set wsTo = Workbooks("Copy and Paste.xls").Worksheets("Sheet2") With wsFrom .Range("A1:Z1057").Copy wsTo.Range("A1").PasteSpecial Paste:=xlPasteValues 'etc End With Application.CutCopyMode = False Workbooks(lsFile).Close SaveChanges:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error - Copying Data between Workbooks
hi
because you defined isfile as a range object. you then Set lsFile = Sheets("WB").Range("B4") which is a range, not a value. you are looking for a value. defined isfile as a string i.e. dim isfile as string and don't set it or you'll bet an object error. isfile = range("B4") 'should do it. and do the above will all the other range object you declared. remember you are not looking for a range address. you are looking for the value in the cell which is a string. Regards FSt1 "NumberCruncher13" wrote: Can someone let me know why I get a type mismatch error when the following code is ran? Sub CopyFromLSS() Dim MyDir As Range, lsFile As Range, csFile As Range Dim wsTo As Worksheet, wsFrom As Worksheet Set MyDir = Sheets("WB").Range("B2") 'C:\06\ Set csFile = Sheets("WB").Range("B3") 'CS.xls Set lsFile = Sheets("WB").Range("B4") 'LS.xls Set wsFrom = Workbooks(lsFile).Worksheets("Sheet1") 'Why is there a type mismatch error? Set wsTo = Workbooks("Copy and Paste.xls").Worksheets("Sheet2") With wsFrom .Range("A1:Z1057").Copy wsTo.Range("A1").PasteSpecial Paste:=xlPasteValues 'etc End With Application.CutCopyMode = False Workbooks(lsFile).Close SaveChanges:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error - Copying Data between Workbooks
On Apr 9, 12:45*pm, FSt1 wrote:
hi because you defined isfile as a range object. you then Set lsFile = Sheets("WB").Range("B4") *which is a range, not a value. you are looking for a value. defined isfile as a string i.e. dim isfile as string and don't set it or you'll bet an object error. isfile = range("B4") * * *'should do it. and do the above will all the other range object you declared. remember you are not looking for a range address. you are looking for the value in the cell which is a string. Regards FSt1 "NumberCruncher13" wrote: Can someone let me know why I get a type mismatch error when the following code is ran? *Sub CopyFromLSS() * * Dim MyDir As Range, lsFile As Range, csFile As Range * * Dim wsTo As Worksheet, wsFrom As Worksheet * * Set MyDir = Sheets("WB").Range("B2") *'C:\06\ * * Set csFile = Sheets("WB").Range("B3") 'CS.xls * * Set lsFile = Sheets("WB").Range("B4") 'LS.xls * * Set wsFrom = Workbooks(lsFile).Worksheets("Sheet1") 'Why is there a type mismatch error? * * Set wsTo = Workbooks("Copy and Paste.xls").Worksheets("Sheet2") * * With wsFrom * * * * .Range("A1:Z1057").Copy * * * * wsTo.Range("A1").PasteSpecial Paste:=xlPasteValues * * * * *'etc * * End With * * *Application.CutCopyMode = False * * *Workbooks(lsFile).Close SaveChanges:=False End Sub- Hide quoted text - - Show quoted text - Thanks so much! That worked. I was always confused when to use the set statement. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error - Copying Data between Workbooks
I was always confused when to use the set statement.
Set is for objects only. Variables of other datatypes (numbers, strings, etc.) require it. Once upon a time you had to use Let statements for non-objects and Set for objects, but those days are behind us and Let is now implied and no longer required (but allowed). -- HTH, George "NumberCruncher13" wrote in message ... On Apr 9, 12:45 pm, FSt1 wrote: hi because you defined isfile as a range object. you then Set lsFile = Sheets("WB").Range("B4") which is a range, not a value. you are looking for a value. defined isfile as a string i.e. dim isfile as string and don't set it or you'll bet an object error. isfile = range("B4") 'should do it. and do the above will all the other range object you declared. remember you are not looking for a range address. you are looking for the value in the cell which is a string. Regards FSt1 "NumberCruncher13" wrote: Can someone let me know why I get a type mismatch error when the following code is ran? Sub CopyFromLSS() Dim MyDir As Range, lsFile As Range, csFile As Range Dim wsTo As Worksheet, wsFrom As Worksheet Set MyDir = Sheets("WB").Range("B2") 'C:\06\ Set csFile = Sheets("WB").Range("B3") 'CS.xls Set lsFile = Sheets("WB").Range("B4") 'LS.xls Set wsFrom = Workbooks(lsFile).Worksheets("Sheet1") 'Why is there a type mismatch error? Set wsTo = Workbooks("Copy and Paste.xls").Worksheets("Sheet2") With wsFrom .Range("A1:Z1057").Copy wsTo.Range("A1").PasteSpecial Paste:=xlPasteValues 'etc End With Application.CutCopyMode = False Workbooks(lsFile).Close SaveChanges:=False End Sub- Hide quoted text - - Show quoted text - Thanks so much! That worked. I was always confused when to use the set statement. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error - Copying Data between Workbooks
I think you left out something important <bg.
Variables of other datatypes (numbers, strings, etc.) _do not_ require it. George Nicholson wrote: I was always confused when to use the set statement. Set is for objects only. Variables of other datatypes (numbers, strings, etc.) require it. Once upon a time you had to use Let statements for non-objects and Set for objects, but those days are behind us and Let is now implied and no longer required (but allowed). -- HTH, George "NumberCruncher13" wrote in message ... On Apr 9, 12:45 pm, FSt1 wrote: hi because you defined isfile as a range object. you then Set lsFile = Sheets("WB").Range("B4") which is a range, not a value. you are looking for a value. defined isfile as a string i.e. dim isfile as string and don't set it or you'll bet an object error. isfile = range("B4") 'should do it. and do the above will all the other range object you declared. remember you are not looking for a range address. you are looking for the value in the cell which is a string. Regards FSt1 "NumberCruncher13" wrote: Can someone let me know why I get a type mismatch error when the following code is ran? Sub CopyFromLSS() Dim MyDir As Range, lsFile As Range, csFile As Range Dim wsTo As Worksheet, wsFrom As Worksheet Set MyDir = Sheets("WB").Range("B2") 'C:\06\ Set csFile = Sheets("WB").Range("B3") 'CS.xls Set lsFile = Sheets("WB").Range("B4") 'LS.xls Set wsFrom = Workbooks(lsFile).Worksheets("Sheet1") 'Why is there a type mismatch error? Set wsTo = Workbooks("Copy and Paste.xls").Worksheets("Sheet2") With wsFrom .Range("A1:Z1057").Copy wsTo.Range("A1").PasteSpecial Paste:=xlPasteValues 'etc End With Application.CutCopyMode = False Workbooks(lsFile).Close SaveChanges:=False End Sub- Hide quoted text - - Show quoted text - Thanks so much! That worked. I was always confused when to use the set statement. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error - Copying Data between Workbooks
oops
Well, it was one missing word. and a short one at that. <sheepish grin -- HTH, George "Dave Peterson" wrote in message ... I think you left out something important <bg. Variables of other datatypes (numbers, strings, etc.) _do not_ require it. George Nicholson wrote: I was always confused when to use the set statement. Set is for objects only. Variables of other datatypes (numbers, strings, etc.) require it. Once upon a time you had to use Let statements for non-objects and Set for objects, but those days are behind us and Let is now implied and no longer required (but allowed). -- HTH, George "NumberCruncher13" wrote in message ... On Apr 9, 12:45 pm, FSt1 wrote: hi because you defined isfile as a range object. you then Set lsFile = Sheets("WB").Range("B4") which is a range, not a value. you are looking for a value. defined isfile as a string i.e. dim isfile as string and don't set it or you'll bet an object error. isfile = range("B4") 'should do it. and do the above will all the other range object you declared. remember you are not looking for a range address. you are looking for the value in the cell which is a string. Regards FSt1 "NumberCruncher13" wrote: Can someone let me know why I get a type mismatch error when the following code is ran? Sub CopyFromLSS() Dim MyDir As Range, lsFile As Range, csFile As Range Dim wsTo As Worksheet, wsFrom As Worksheet Set MyDir = Sheets("WB").Range("B2") 'C:\06\ Set csFile = Sheets("WB").Range("B3") 'CS.xls Set lsFile = Sheets("WB").Range("B4") 'LS.xls Set wsFrom = Workbooks(lsFile).Worksheets("Sheet1") 'Why is there a type mismatch error? Set wsTo = Workbooks("Copy and Paste.xls").Worksheets("Sheet2") With wsFrom .Range("A1:Z1057").Copy wsTo.Range("A1").PasteSpecial Paste:=xlPasteValues 'etc End With Application.CutCopyMode = False Workbooks(lsFile).Close SaveChanges:=False End Sub- Hide quoted text - - Show quoted text - Thanks so much! That worked. I was always confused when to use the set statement. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data type mismatch error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Type Mismatch Error when getting data from another workbook | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Copying data to another worksheet gives "Type Mismatch" error | Excel Programming |