copying from one file paste in another

[tim64]

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

 

[bhofsetz]

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

 

[Jim Thomlinson]

Remove the .Select from the Set statement something like this

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

 

[tim64]

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.