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

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

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.

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.

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

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