Copying to separate workbook marco not working

[Will_iam]

Hi,

I have the below marco

Please Login or Register  to view this content.

Which basically copies the referenced cells from one workbook called "Web Update Request" to another workbook called "reports & updates log".

I keep getting a runtime error 9 on this line

Please Login or Register  to view this content.

and im not sure why? The stranger thing is, is that it used to work just fine until yesterday.

Any help would be great

Thanks.




EDIT, I figured it out. My PC settings had been changed to display file extensions, and thats what was missing.

[nathansav]

can you attach the sheet?

[Will_iam]

workbooks Attached.

[arlu1201]

You need to put the file extension in this part of that line -

Please Login or Register  to view this content.

[Will_iam]

Thanks for the reply, but that didnt work. Still get the same error.

One other thing I thought i would mention, is that it does find the workbook in the first instance, so from this part,

Please Login or Register  to view this content.

As it opens the workbook, its just the actual copying of the cells.

[Norie]

Why aren't you using wbTarget in the copying code?

[Will_iam]

Why aren't you using wbTarget in the copying code?

Where do you mean?

[ragulduy]

you set the workbook to a variable and then reference it as workbooks("workbook name") again. You could use:

Please Login or Register  to view this content.

Note - Arlu's suggestion is correct, when you add the file extension to your code it works for me, so the changing the reference to wbTarget should also remove the error.

[Norie]

Wherever you have this,

Please Login or Register  to view this content.

replace it with wbTarget.

[Will_iam]

you set the workbook to a variable and then reference it as workbooks("workbook name") again. You could use:

Please Login or Register  to view this content.

Note - Arlu's suggestion is correct, when you add the file extension to your code it works for me, so the changing the reference to wbTarget should also remove the error.

Thanks for your help with this, your suggestion did resolve the issue with that line of code, but now it is just producing the same error on the next line

Please Login or Register  to view this content.

Which im assuming will mean it will be the same problem for the remaining lines as well. I have tried applying similar logic to it as you already mentioned, but to no avail.

Im affraid my Vb skills arent to great.

[Norie]

You need to replace all instances of this with wbTarget.

Please Login or Register  to view this content.

[Will_iam]

You need to replace all instances of this with wbTarget.

Please Login or Register  to view this content.

Thanks, I have tried that, and it doesnt make any difference?

[Norie]

Can you post exactly what you did and tell us how it doesn't work?

This is probably the easiest way to do it.

Please Login or Register  to view this content.

[Will_iam]

This is what it looks like now.

Please Login or Register  to view this content.

I replaced the "Workbook" with wbTarget as you mentioned.

It is still producing the runtime error 9, but now on a differnet line.

Please Login or Register  to view this content.

The "Reports & Updates Log" opens fine, it just then doesnt copy anything across, and just errors.

[ragulduy]

Please Login or Register  to view this content.

[Will_iam]

Please Login or Register  to view this content.

Still produces the runtime error on the same line.....

Seriously is VB just broken?

What I really don't get is that, it worked fine last week. Then this week... nope, and im pretty sure I havent changed anything.

I really appreciate the help though guys.

[Norie]

Is this the workbook the code is in?

Please Login or Register  to view this content.

If it is replace it throughout the code with ThisWorkbook.

Please Login or Register  to view this content.

If it's not try replacing it with Workbooks("Web Update Request.xlsm")

Please Login or Register  to view this content.

[Will_iam]

[QUOTE=Norie;3219610]Is this the workbook the code is in?
QUOTE]

Thanks for your help, the problem was the missing file extensions. But I figured it out just before you posted.

But thanks anyway!

[Norie]

The way to avoid that is not to use the workbook names to refer to them, use references.