Copying large worksheets in VBA

[TaylorA]

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

 

[Norie]

Taylor

How is it not working/causing problems?

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]

It gives me a 'Subscript out of range' error on the line

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

 

[KenCriss]

Are you sure you have a "Sheet1" in your workbook?

 

[TaylorA]

Well I got it fixed.. in case you care, I fixed the line above by doing the following..

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

..where Main is the only other worksheet in the workbook. [/code]