Copying large worksheets in VBA

TaylorA

New Member
Joined
Sep 22, 2006
Messages
3
Hi,

I'm writing some code that will automate a routine task, but I'm having some trouble. I want to import a specific worksheet into my main file, perform some data manipulation, then put the new information into a report. Everything is working fine except for the actual importing of the worksheet. The code for that part is as follows..

---------------------------------------------------------------------------------
Sub openFile()

Dim myfile As Variant, otherFile As String

myfile = Application.GetOpenFilename("All Files,*.*")
If myfile = False Then
Exit Sub
End If

Workbooks.Open Filename:=myfile
otherFile = ActiveWorkbook.Name

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks("Component Seed Status Template.xls").Sheets(Worksheets.Count)
Workbooks(otherFile).Close

End Sub

-----------------------------------------------------------------------------

This code works perfectly fine on small sheets, but the actual sheet I want to import seems to be too large (it is approx. 15 megs / 36000 rows). The strange thing is that moving the sheet manually works just fine.

Ideas? Any help is much appreciated!

Thanks,
Taylor
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,830
Office Version
  1. 365
Platform
  1. Windows
Taylor

How is it not working/causing problems?
Code:
Sub openFile()
Dim myfile As Variant, otherFile As Workbook

    myfile = Application.GetOpenFilename("All Files,*.*")
    If myfile = False Then
        Exit Sub
    End If
    
    Set otherFile = Workbooks.Open(Filename:=myfile)
    otherFile.Sheets("Sheet1").Copy After:=Workbooks("Component Seed Status Template.xls").Sheets(Worksheets.Count)
    otherFile.Close

End Sub
 

TaylorA

New Member
Joined
Sep 22, 2006
Messages
3
It gives me a 'Subscript out of range' error on the line

Code:
Sheets("Sheet1").Copy After:=Workbooks("Component Seed Status Template.xls").Sheets(Worksheets.Count)

Any thoughts? It's strange that it works fine on smaller workbooks, but then fails on a bigger one.

Thanks
Taylor
 

TaylorA

New Member
Joined
Sep 22, 2006
Messages
3
Well I got it fixed.. in case you care, I fixed the line above by doing the following..

Code:
Sheets("Sheet1").Copy After:=Workbooks("Component Seed Status Template.xls").Worksheets("Main")
..where Main is the only other worksheet in the workbook. [/code]
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,058
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top