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" 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" 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" 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" wrote in message
...
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" 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" wrote in message
...
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" wrote in message
...
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" 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" wrote in message
...
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" 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" wrote in message
...
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" 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