copying from one file paste in another
 

I have this code that is suppose to open the file copy all its content
and then paste it in the file the code is in. But the code has an erro
(see below)



Sub CombineFiles()

Range("A1").Select

MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = Application.GetOpenFilename

Workbooks.Open Filename:=MySource

Range("A1").Select
Set MyRange = Range(Selection
ActiveCell.SpecialCells(xlLastCell)).Selec
<--------------------------------- error: type mismatch

MyRange.Copy
ActiveWorkbook.Close
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs MySource
End Su

--
tim6
-----------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...fo&userid=2329
View this thread: http://www.excelforum.com/showthread.php?threadid=38459

copying from one file paste in another
 

Here is a slightly modified version of your code which should take care
of the error you were getting (see comments for changes made).


Code:
--------------------
Sub CombineFiles()

Range("A1").Select

MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = Application.GetOpenFilename

Workbooks.Open Filename:=MySource

Range("A1").Select
Set myRange = Range(Selection, ActiveCell.SpecialCells(xlCellTypeLastCell)) '<-Solution : Take out the select here because you are setting the myRange with this line

myRange.Copy
Application.DisplayAlerts = False '<-- This will save you from having Excel notify you that you placed a large amount of info on the clipboard before closing the source file
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste
ActiveWorkbook.Save 'SaveAs MySource <-- this is saving the Activeworkbook as the Source workbook (I don't think that is what you want to do?)
End Sub
--------------------


HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=384599

copying from one file paste in another
 

Remove the .Select from the Set statement something like this

Set MyRange = Range(Selection, ActiveCell.SpecialCells(xlLastCell))

--
HTH...

Jim Thomlinson


"tim64" wrote:


I have this code that is suppose to open the file copy all its contents
and then paste it in the file the code is in. But the code has an error
(see below)



Sub CombineFiles()

Range("A1").Select

MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = Application.GetOpenFilename

Workbooks.Open Filename:=MySource

Range("A1").Select
Set MyRange = Range(Selection,
ActiveCell.SpecialCells(xlLastCell)).Select
<--------------------------------- error: type mismatch

MyRange.Copy
ActiveWorkbook.Close
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs MySource
End Sub


--
tim64
------------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...o&userid=23295
View this thread: http://www.excelforum.com/showthread...hreadid=384599

copying from one file paste in another
 

It works. thank you. (you may not understand it but I wanted it to b
saved as the source, I changed it back on my own so its ok.

--
tim6
-----------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...fo&userid=2329
View this thread: http://www.excelforum.com/showthread.php?threadid=38459