Problem with vb code when copying and pasting

Hi,

I have written the code below to copy a range from a saved workbook and to paste it into the active workbook that the project is saved in:

Please Login or Register  to view this content.

It works perfectly but I need to select another range from the same saved workbook so I re-wrote the code as follows:

Please Login or Register  to view this content.

When I run this code I get the error message:

Run-time error '-2147221080 (80040198)':
Method 'sheets' of object '_workbook' failed

The line "Wb1.Sheets(1).Range("L1:L10").Copy' is highlighted in yellow when I go to de-bug.

Can anyone tell me where I am going wrong please?

Thanks very much in advance

Michael

Hi,


Since you're new here you'll see that on this occasion I've edited your post and put code tags around your code. This is one of the forum rules. Please remember on other occasions.

The line is failing because you've already closed Wb1.

Hi Michael and welcome to the forum.

You need to learn to debug your code by putting in a breakpoint and stepping through the code a line at a time. Here is a great site for that:
http://www.cpearson.com/excel/Debug.htm

Then in looking at your code you close the workbook at the Wb1.Close line so when you get to the next Wb1.Sheets(1) it can't access a closed workbook.
You will need to close the workbook after you're done with all the copy and paste steps.
Read http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm that explains some of what I'm saying.

I have already mentioned the fix in my last comment. Implying you should close Wb1 after the paste.