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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,030
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,030
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,810
Messages
5,483,042
Members
407,375
Latest member
achusp

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top