Open a workbook and copy the first sheet into a defined sheet in excel vba

[tmd63]

Hi,
I am trying to write some code to load a sheet from another excel workbook into excel. But although I can open the file, I get a 400 error and the sheet does not copy.

Code:
Application.ScreenUpdating = False
Dim MyFile As String
MyFile = Application.GetOpenFilename()
Workbooks.Open (MyFile)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:P" & LastRow).Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Worksheets("CM11").Select
Range("A1").Select
Worksheets("CM11").Paste

It appears to be a fault at the LastRow line as the xlUp appears to start at -4162 and this returns a LastRow value of 1.

Can anyone enlighten me as to why the fault is happening.
(This is in an Excel 2003)

 

[JLGWhiz]

I don't know that initializing the LastRow variable would cause a 400 error which normally occurs when a user attempts to open a form or file that is already open. But if the LastRow variable value is returning a 1 then it indicates that the column being used to find the last row is either empty or only has data in row 1. A better way of finding the last row might be.

Dim LastRow As Long
LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row

This would give you the last row with any data in it for whichever column has the most entries, including cells with formulas that may not be currently displaying a value. If you don't want to use that broad of a coverage for the last row, then pick the column that best suits your purpose and replace the "A" in the current code line with that column reference.

 

[tmd63]

Hi, Thanks this has worked to a degree. I now do not get an error 400 at the line 'LastRow'.
But I do get an error 400 on the next line 'Range("A1:P" & LastRow).Select'.
I will try and use the more explicit form of 'ActiveSheet.Range' instead of just 'Range' and see if that is the cause.

 

[tmd63]

Ok. I have replaced both 'Range' with 'ActiveSheet.Range' and the code moves further, but I still get a 400 error.

 

[JLGWhiz]

You might have to step through the procedure one line at a time to see where the actual fault occurs. You can do that by opening the VB Editor, click anywhere inside the body of the macro then use function key F8 to step through the procedure. You will see a highlight on the line that will execute when you press the F8 key. If an error occurs, the highlight will not move from the line that caused the error, if it goes to debug mode. Else, it might just end the procedure in other circumstances and the highlight will disappear. Once you have defined the exact line of code on which the error occurs, analyze what action should have occurred on that line of code and then analyze your worksheet or form data to be sure it is in order.