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

tmd63

New Member
Joined
Feb 21, 2014
Messages
33
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)
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,273
Office Version
2013
Platform
Windows
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.
Code:
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

New Member
Joined
Feb 21, 2014
Messages
33
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

New Member
Joined
Feb 21, 2014
Messages
33
Ok. I have replaced both 'Range' with 'ActiveSheet.Range' and the code moves further, but I still get a 400 error.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,273
Office Version
2013
Platform
Windows
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,086
Messages
5,509,167
Members
408,711
Latest member
EMexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top