Large amount of information copied to clipboard message box.

[Tucker92]

I am trying to copy a large amount of information from one spread sheet to another but i need to close the first spreadsheet down before i paste the information When i do this i get that annoying message box pop up and have to press yes everytime. Is there anyway i can do it using my vba.

I tried to use the Application.DisplayAlerts = False and true but they only hide it and then i cannot paste my information afterwards.
And i cant clear my clipboard until i have pasted it.

Thank you

 

[pbornemeier]

This code worked for me to copy data from a Book2.xlsx, close the workbook then paste it to the workbook holding the code:

    Windows("Book2.xlsx").Activate
    Range("A1:H50").Copy
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Range("G12").Select
    ActiveSheet.Paste

With Excel 2016 & Windows 10, code in a standard module

 

[Yongle]

Another way would be to use an intermediate temporary workbook
Something like this

Sub CopyLarge()
    Application.ScreenUpdating = False
    Const path1 = "C:\Test\CopyFromThis.xlsx", path2 = "C:\Test\PasteToThis.xlsx"
    Dim wb As Workbook, temp As Workbook, rng As Range
[I][COLOR=#800080]'open first workbook and set range to be copied[/COLOR][/I]
    Set wb = Workbooks.Open(path1)
    Set rng = wb.Sheets("Sheetname").Cells(1).CurrentRegion
[I][COLOR=#800080]'add temporary workbook \ copy to temp \ close first workbook[/COLOR][/I]
    Set temp = Workbooks.Add
    With temp.Sheets(1)
        rng.Copy .Cells(1)
        Application.CutCopyMode = False
        Set rng = .Cells(1).CurrentRegion
    End With
    wb.Close True
[I][COLOR=#800080]'open second workbook\ copy from Temp \ close temp[/COLOR][/I]
    Set wb = Workbooks.Open(path2)
    With wb.Sheets("PasteSheet").Range("A1")
        rng.Copy .Cells(1)
        Application.CutCopyMode = False
    End With
    temp.Close False
    wb.Save
    Application.ScreenUpdating = True
End Sub