Copy from 2 Excel files into another

[vgarzon]

Hi,

I'm trying to include a button with a macro that opens a selected file, copies the data and pastes it on a Sheet of the original file.

I think I'm close, but when trying to paste I receive an error. I don't know if that's the correct way so I'm open to new ways of doing this.

The strange part is that If I stop on the pasting step and continue debugging step by step, it works fine.

See original post in: http://www.ozgrid.com/forum/showthread.php?t=172591


I'm using Excel 2010 and here's what I do:

Sub pasteFromFile() 
    Sheet15.Activate 
    Cells.ClearContents 
     'Open a user-selectable MS Excel file
    fileOpenName = Application.GetOpenFilename(filefilter:="MS Excel Files, *.xls;*.xlsx") 
     'fileSaveName = Application.GetSaveAsFilename(filefilter:="xlsx Files (*.xlsx), *.xlsx")
    If fileOpenName = False Then 
        MsgBox "Please select a valid MS Excel file to load" 
    Else 
        Application.ScreenUpdating = False 
        Application.DisplayAlerts = False 
         
        Dim obXL As Object 
         
        Set obXL = New Excel.Application 
        obXL.Visible = False 'True
        obXL.Workbooks.Open fileOpenName 
         'Copy the whole source sheet data
        Application.CutCopyMode = False 
        obXL.Cells.Copy 
         
         'Paste it on the destination tab
        Windows(1).Activate 
        Sheet15.Activate 
        Sheet15.Range("A1").Select 
        ActiveSheet.Paste 
         
        Application.CutCopyMode = False 
         
         'Close the background open file
        obXL.Quit 
         
         'Go back to the instructions
        Sheet18.Activate 
         
        Application.ScreenUpdating = True 
        Application.DisplayAlerts = True 
    End If 
     
End Sub