Workbooks.Open Filename:=FilesToOpen
Sheets().Copy After:=ThisWorkbook.Sheets("Instr")
Workbooks.Open Filename:=FilesToOpen
In this line you are opening the csv file in excel 2007 ... in other words saving it to a sheet that has 1million lines
Sheets().Copy After:=ThisWorkbook.Sheets("Instr")
In this line of code you're attempting to move/copy the entire sheet (i.e. 1 million rows) to a excel 2003 wookbook that cannot contain that many rows.
SOLUTION:
Do not attempt to move the entire sheet , instead select the range of data and then only copy/paste the selected range of cells. ... If you could indicate what column , on the source, can be used to determine how many rows have data PLUS how many columns AND the name of the Target sheet , then someone here could help you with the few lines of code it would take to solve the problem. ( see example below)
Public Sub LastRowAndColumnOfSheet()
Dim CopyRange As Range
'FIND END OF DATA OF ACTIVE SHEET
LastRow = Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastColumn = Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'SET COPY RANGE OF ACTIVE SHEET
Set CopyRange = Range(Cells(1, 1), Cells(LastRow, LastColumn))
'COPY/PASTE TO THE WORKBOOK THAT HAS THE MACRO
CopyRange.Copy
ActiveSheet.Paste Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A1")
'CLEAN-UP
Application.CutCopyMode = False
Set CopyRange = Nothing
End Sub
Bookmarks