Type Mismatch error

[Jeff Wright]

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 Bovey]

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

 

[Bob Phillips]

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)

 

[Tim Zych]

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

 

[Tim Zych]

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

 

[Guest]

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