Excel 2003 macro sheet copy/move error on Excel 2010

[EDN Admin]

Hi
I have a macro that works on excel 2003 but doesnt seems to work in excel 2010. It basically run through a collection of txt files, copy the data across before copying the sheet into the active workbook. It flags up a run time error (Excel cannot insert
the sheet into destination workbook, because it contains fewer rows and columns than the source workbook.... ) when it tried to process the line that move the sheet across. If anyone can advice how to get round the problem. Many thanks.
<pre class="prettyprint compile Macro
Macro recorded 30/04/2012 by Administrator


initialisation
With Application
.Calculation = xlManual
End With

treat_workbook = ActiveWorkbook.Name
treat_sheet = ActiveSheet.Name
measurement_files = Application.GetOpenFilename("measurement file (*.txt), *.txt", , , , True)
nb_files = UBound(measurement_files)

Application.ScreenUpdating = False

-----------------------LOOP ON MEASUREMENTS FILE-------------------
For file = 1 To nb_files

measurement_file = measurement_files(file)
Workbooks.Open measurement_file
measure_workbook = ActiveWorkbook.Name
measure_sheet = ActiveSheet.Name
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks(treat_workbook).Activate
Sheets(treat_sheet).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

move measurement worksheet in treatment workbook
Workbooks(measure_workbook).Activate
Sheets(measure_sheet).Move After:=Workbooks(treat_workbook).Sheets(file)

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

Next file

Application.ScreenUpdating = True

With Application
.Calculation = xlAutomatic
End With



End Sub[/code]
<br/>

Regards
Hon

View the full article