Copy large sheet in Excel

Question

Recently, I encountered a problem which seems to be quite simple to resolve, but I'm new to VBA and due to this I need any advice. I'm trying to write a macro in VBA which copies a very large sheet (around 140k of lines).

I tried different approaches based on the following topics:

My current solution is:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim wb As Workbook
Set wb = ActiveWorkbook
With Workbooks.Open(FileName, ReadOnly:=True)
    .Sheets(1).Visible = -1
    .Sheets(1).Copy before:=wb.Sheets(1)
     wb.Sheets(1).Name = "Name"

    .Close False
End With

When I'm running this macros - Excel is not responding and program is not opening the file. My question is, is it any possibility to copy a large sheet using standard VBA methods? The code sample works absolutely fine with smaller files.

Does the said sheet have lot of formulas? Did you try turning off screenUpdating, Calculation, event handling, etc etc...? — A.S.H
Could be just a simple matter of not having enough on-board memory to handle the transaction - perhaps there's a lot swap file activity? Are you using 32 bit Excel? 64-bit will provide better utilization of memory. I know, I touching on the obvious, and if you're stuck with 32-bit (and limited memory), well, your stuck with 32-bit (and limited memory) :( — Bill Roberts

Shawn Cartwright Shawn Cartwright · Accepted Answer · 2017-04-28T11:08:14

Try. It works fine for me.

 Sub rten()


 Dim wb As Workbook
 Dim docname As String

 docname = "test1"

    Set wb = ActiveWorkbook
    With Workbooks.Open(docname, ReadOnly:=True)
        .Sheets(1).Visible = -1
        .Sheets(1).Copy before:=wb.Sheets(1)
         wb.Sheets(1).Name = "Name"

        .Close False
    End With


 End Sub

It might be because you have a sheet called "Name" that its crashing. Have you checked to see if you already have it there?

Copy large sheet in Excel

1 Answers