Type Mismatch error

Greetings!

I don't understand why I'm getting a "Type Mismatch" error in the routine
below, which I get on the line, "Set Temp = Filename". The purpose of this
macro (although not fully shown here) is to save just the pertinent data
from the main workbook to a new workbook, whose file name is entered by the
user. After the user creates a filename, how can I code the macro to
activate the new workbook?

Any help is greatly appreciated.

Thanks,

Jeff

Sub GetDataFromMain()
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\JeffW\My Documents\Jeff's documents\Excel
files\Temp.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Set Temp = Filename
Windows("Main.xls").Activate
Range("A1:A10").Select
Selection.Copy
Windows(Filename).Activate
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End Sub

Hi Jeff,

Based on how you use it in your code it looks like Filename holds a
string. You cannot use the Set statement to assign string data to another
variable. If the Temp variable is supposed to hold the string contained in
Filename then change your code to this:

Temp = Filename

If your Temp variable is supposed to hold an object reference to the
workbook named by the Filename variable then change your code to this:

Set Temp = Workbooks(Filename)

I can't be absolutely sure the above will work properly because I don't know
exactly what the Filename variable contains.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Jeff Wright" <[email protected]> wrote in message
news:faQae.30430$lv1.11496@fed1read06...
> Greetings!
>
> I don't understand why I'm getting a "Type Mismatch" error in the routine
> below, which I get on the line, "Set Temp = Filename". The purpose of this
> macro (although not fully shown here) is to save just the pertinent data
> from the main workbook to a new workbook, whose file name is entered by
> the user. After the user creates a filename, how can I code the macro to
> activate the new workbook?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> Jeff
>
> Sub GetDataFromMain()
> Workbooks.Add
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\JeffW\My Documents\Jeff's
> documents\Excel files\Temp.xls" _
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Set Temp = Filename
> Windows("Main.xls").Activate
> Range("A1:A10").Select
> Selection.Copy
> Windows(Filename).Activate
> ActiveSheet.Paste
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> Application.CutCopyMode = False
> End Sub
>
>

Jeff,

Nowhere in your code is the value of Filename loaded, so the Set Temp =
Filename is like saying Set Temp = Empty. The previous line, SaveAs,
specifies the filename to save as, but that does not load a variable called
Filename.

Then, if you put a value in Filename, what are you using Temp for. Set is
the way to load an object variable, but the Filename will be a string, so
this does not make sense.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff Wright" <[email protected]> wrote in message
news:faQae.30430$lv1.11496@fed1read06...
> Greetings!
>
> I don't understand why I'm getting a "Type Mismatch" error in the routine
> below, which I get on the line, "Set Temp = Filename". The purpose of this
> macro (although not fully shown here) is to save just the pertinent data
> from the main workbook to a new workbook, whose file name is entered by
the
> user. After the user creates a filename, how can I code the macro to
> activate the new workbook?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> Jeff
>
> Sub GetDataFromMain()
> Workbooks.Add
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\JeffW\My Documents\Jeff's
documents\Excel
> files\Temp.xls" _
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Set Temp = Filename
> Windows("Main.xls").Activate
> Range("A1:A10").Select
> Selection.Copy
> Windows(Filename).Activate
> ActiveSheet.Paste
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> Application.CutCopyMode = False
> End Sub
>
>

Sub GetDataFromMain()
Set Temp = Workbooks.Add
Temp.SaveAs Filename:="C:\Documents and Settings\JeffW\My
Documents\Jeff's documents\Excel files\Temp.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("Main.xls").Activate
Range("A1:A10").Select
Selection.Copy
Temp.Activate
Temp.Paste
Temp.Save
Temp.Close
Application.CutCopyMode = False
End Sub


"Jeff Wright" <[email protected]> wrote in message
news:faQae.30430$lv1.11496@fed1read06...
> Greetings!
>
> I don't understand why I'm getting a "Type Mismatch" error in the routine
> below, which I get on the line, "Set Temp = Filename". The purpose of this
> macro (although not fully shown here) is to save just the pertinent data
> from the main workbook to a new workbook, whose file name is entered by
the
> user. After the user creates a filename, how can I code the macro to
> activate the new workbook?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> Jeff
>
> Sub GetDataFromMain()
> Workbooks.Add
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\JeffW\My Documents\Jeff's
documents\Excel
> files\Temp.xls" _
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Set Temp = Filename
> Windows("Main.xls").Activate
> Range("A1:A10").Select
> Selection.Copy
> Windows(Filename).Activate
> ActiveSheet.Paste
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> Application.CutCopyMode = False
> End Sub
>
>

Sub GetDataFromMain()
Set temp = Workbooks.Add
temp.SaveAs Filename:="C:\Documents and Settings\JeffW\My
Documents\Jeff's documents\Excel files\Temp.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("Main.xls").Activate
Range("A1:A10").Select
Selection.Copy
temp.Activate
ActiveSheet.Paste
temp.Save
temp.Close
Application.CutCopyMode = False
End Sub


"Tim Zych" <[email protected]> wrote in message
news:[email protected]...
> Sub GetDataFromMain()
> Set Temp = Workbooks.Add
> Temp.SaveAs Filename:="C:\Documents and Settings\JeffW\My
> Documents\Jeff's documents\Excel files\Temp.xls" _
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Workbooks("Main.xls").Activate
> Range("A1:A10").Select
> Selection.Copy
> Temp.Activate
> Temp.Paste
> Temp.Save
> Temp.Close
> Application.CutCopyMode = False
> End Sub
>
>
> "Jeff Wright" <[email protected]> wrote in message
> news:faQae.30430$lv1.11496@fed1read06...
> > Greetings!
> >
> > I don't understand why I'm getting a "Type Mismatch" error in the
routine
> > below, which I get on the line, "Set Temp = Filename". The purpose of
this
> > macro (although not fully shown here) is to save just the pertinent data
> > from the main workbook to a new workbook, whose file name is entered by
> the
> > user. After the user creates a filename, how can I code the macro to
> > activate the new workbook?
> >
> > Any help is greatly appreciated.
> >
> > Thanks,
> >
> > Jeff
> >
> > Sub GetDataFromMain()
> > Workbooks.Add
> > ActiveWorkbook.SaveAs Filename:= _
> > "C:\Documents and Settings\JeffW\My Documents\Jeff's
> documents\Excel
> > files\Temp.xls" _
> > , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> > ReadOnlyRecommended:=False, CreateBackup:=False
> > Set Temp = Filename
> > Windows("Main.xls").Activate
> > Range("A1:A10").Select
> > Selection.Copy
> > Windows(Filename).Activate
> > ActiveSheet.Paste
> > ActiveWorkbook.Save
> > ActiveWorkbook.Close
> > Application.CutCopyMode = False
> > End Sub
> >
> >
>
>

try this:
Option Explicit
Sub GetDataFromMain()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsMain As Worksheet

Set wb = Workbooks.Add
Set ws = wb.ActiveSheet
Set wsMain = Windows("Main.xls").Worksheets("Sheet1")
With wsMain.Range("A1:A10")
ws.Range("a1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

wb.SaveAs Filename:="C:\Excel files\Temp.xls"
wb.Close False

End Sub

"Jeff Wright" wrote:

> Greetings!
>
> I don't understand why I'm getting a "Type Mismatch" error in the routine
> below, which I get on the line, "Set Temp = Filename". The purpose of this
> macro (although not fully shown here) is to save just the pertinent data
> from the main workbook to a new workbook, whose file name is entered by the
> user. After the user creates a filename, how can I code the macro to
> activate the new workbook?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> Jeff
>
> Sub GetDataFromMain()
> Workbooks.Add
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\JeffW\My Documents\Jeff's documents\Excel
> files\Temp.xls" _
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Set Temp = Filename
> Windows("Main.xls").Activate
> Range("A1:A10").Select
> Selection.Copy
> Windows(Filename).Activate
> ActiveSheet.Paste
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> Application.CutCopyMode = False
> End Sub
>
>
>

Thanks, Tim

I guess my problem was with the "Set" command. My routine is now working!!

Thanks for your help.

Jeff

"Tim Zych" <[email protected]> wrote in message
news:[email protected]...
> Sub GetDataFromMain()
> Set temp = Workbooks.Add
> temp.SaveAs Filename:="C:\Documents and Settings\JeffW\My
> Documents\Jeff's documents\Excel files\Temp.xls" _
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> Workbooks("Main.xls").Activate
> Range("A1:A10").Select
> Selection.Copy
> temp.Activate
> ActiveSheet.Paste
> temp.Save
> temp.Close
> Application.CutCopyMode = False
> End Sub
>
>
> "Tim Zych" <[email protected]> wrote in message
> news:[email protected]...
>> Sub GetDataFromMain()
>> Set Temp = Workbooks.Add
>> Temp.SaveAs Filename:="C:\Documents and Settings\JeffW\My
>> Documents\Jeff's documents\Excel files\Temp.xls" _
>> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
>> ReadOnlyRecommended:=False, CreateBackup:=False
>> Workbooks("Main.xls").Activate
>> Range("A1:A10").Select
>> Selection.Copy
>> Temp.Activate
>> Temp.Paste
>> Temp.Save
>> Temp.Close
>> Application.CutCopyMode = False
>> End Sub
>>
>>
>> "Jeff Wright" <[email protected]> wrote in message
>> news:faQae.30430$lv1.11496@fed1read06...
>> > Greetings!
>> >
>> > I don't understand why I'm getting a "Type Mismatch" error in the
> routine
>> > below, which I get on the line, "Set Temp = Filename". The purpose of
> this
>> > macro (although not fully shown here) is to save just the pertinent
>> > data
>> > from the main workbook to a new workbook, whose file name is entered by
>> the
>> > user. After the user creates a filename, how can I code the macro to
>> > activate the new workbook?
>> >
>> > Any help is greatly appreciated.
>> >
>> > Thanks,
>> >
>> > Jeff
>> >
>> > Sub GetDataFromMain()
>> > Workbooks.Add
>> > ActiveWorkbook.SaveAs Filename:= _
>> > "C:\Documents and Settings\JeffW\My Documents\Jeff's
>> documents\Excel
>> > files\Temp.xls" _
>> > , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
>> > ReadOnlyRecommended:=False, CreateBackup:=False
>> > Set Temp = Filename
>> > Windows("Main.xls").Activate
>> > Range("A1:A10").Select
>> > Selection.Copy
>> > Windows(Filename).Activate
>> > ActiveSheet.Paste
>> > ActiveWorkbook.Save
>> > ActiveWorkbook.Close
>> > Application.CutCopyMode = False
>> > End Sub
>> >
>> >
>>
>>
>
>

Bob, thanks for replying. I didn't have a clear understanding of "Set"
before, and now I think I do. I studied my code, fixed it, and now it seems
to be working well.

Thanks again.

Jeff

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Jeff,
>
> Nowhere in your code is the value of Filename loaded, so the Set Temp =
> Filename is like saying Set Temp = Empty. The previous line, SaveAs,
> specifies the filename to save as, but that does not load a variable
> called
> Filename.
>
> Then, if you put a value in Filename, what are you using Temp for. Set is
> the way to load an object variable, but the Filename will be a string, so
> this does not make sense.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jeff Wright" <[email protected]> wrote in message
> news:faQae.30430$lv1.11496@fed1read06...
>> Greetings!
>>
>> I don't understand why I'm getting a "Type Mismatch" error in the routine
>> below, which I get on the line, "Set Temp = Filename". The purpose of
>> this
>> macro (although not fully shown here) is to save just the pertinent data
>> from the main workbook to a new workbook, whose file name is entered by
> the
>> user. After the user creates a filename, how can I code the macro to
>> activate the new workbook?
>>
>> Any help is greatly appreciated.
>>
>> Thanks,
>>
>> Jeff
>>
>> Sub GetDataFromMain()
>> Workbooks.Add
>> ActiveWorkbook.SaveAs Filename:= _
>> "C:\Documents and Settings\JeffW\My Documents\Jeff's
> documents\Excel
>> files\Temp.xls" _
>> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
>> ReadOnlyRecommended:=False, CreateBackup:=False
>> Set Temp = Filename
>> Windows("Main.xls").Activate
>> Range("A1:A10").Select
>> Selection.Copy
>> Windows(Filename).Activate
>> ActiveSheet.Paste
>> ActiveWorkbook.Save
>> ActiveWorkbook.Close
>> Application.CutCopyMode = False
>> End Sub
>>
>>
>
>

Rob,

I really appreciate your reply, as well as the replies of others to my
question. You pointed out the faulty use of "Set" in my code, which I now
understand, and my routine now runs the way I want it to.

Thanks again,

Jeff

"Rob Bovey" <[email protected]> wrote in message
news:[email protected]...
> Hi Jeff,
>
> Based on how you use it in your code it looks like Filename holds a
> string. You cannot use the Set statement to assign string data to another
> variable. If the Temp variable is supposed to hold the string contained in
> Filename then change your code to this:
>
> Temp = Filename
>
> If your Temp variable is supposed to hold an object reference to the
> workbook named by the Filename variable then change your code to this:
>
> Set Temp = Workbooks(Filename)
>
> I can't be absolutely sure the above will work properly because I don't
> know exactly what the Filename variable contains.
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
> "Jeff Wright" <[email protected]> wrote in message
> news:faQae.30430$lv1.11496@fed1read06...
>> Greetings!
>>
>> I don't understand why I'm getting a "Type Mismatch" error in the routine
>> below, which I get on the line, "Set Temp = Filename". The purpose of
>> this macro (although not fully shown here) is to save just the pertinent
>> data from the main workbook to a new workbook, whose file name is entered
>> by the user. After the user creates a filename, how can I code the macro
>> to activate the new workbook?
>>
>> Any help is greatly appreciated.
>>
>> Thanks,
>>
>> Jeff
>>
>> Sub GetDataFromMain()
>> Workbooks.Add
>> ActiveWorkbook.SaveAs Filename:= _
>> "C:\Documents and Settings\JeffW\My Documents\Jeff's
>> documents\Excel files\Temp.xls" _
>> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
>> ReadOnlyRecommended:=False, CreateBackup:=False
>> Set Temp = Filename
>> Windows("Main.xls").Activate
>> Range("A1:A10").Select
>> Selection.Copy
>> Windows(Filename).Activate
>> ActiveSheet.Paste
>> ActiveWorkbook.Save
>> ActiveWorkbook.Close
>> Application.CutCopyMode = False
>> End Sub
>>
>>
>
>