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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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]
 

Forum statistics

Threads
1,141,221
Messages
5,705,100
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top