Macro not running on all/correct sheet

[Seraph84]

Hi

I have a workbook with two worksheets. The first worksheet, "Source", basically just has some editing done to it to end up with two columns, and the second worksheet, "Rates", does a vlookup from the first sheet.

The problem I'm experiencing is that the code only works as its supposed to when "Source" is the active sheet. If I run with the "Rates" sheet as active, no editing is done to "Source" sheet. What do I need to do so that the user is able run the macro with the "Rates" sheet open, instead of having to launch it from the "Source" sheet? I plan on adding a button the to the ribbon.

On a side note, when I only have the "Source" code in the book, the macro to edit runs in under a second. Once I add in the "Rates" code, it takes noticeably longer. Is it my code perhaps?

Here's the code:

Please Login or Register  to view this content.

Thanks for the help!

[cytop]

You need to ensure the sheet you want to process is active before running code against it. Alternatively, declare a couple of worksheet variables, set them to reference the sheets and use those references rather than relying on the activesheet being the sheet to process...

Please Login or Register  to view this content.

You need to qualify each reference to a range in the code.

[JosephP]

by the way

Please Login or Register  to view this content.

needs to be

Please Login or Register  to view this content.

[arlu1201]

I have edited your code -

Please Login or Register  to view this content.

Also you do not need so many lrow variables. You can use just 1 and then have it re-calculate each time you need to use it.

For e.g. this piece of code which i corrected

Please Login or Register  to view this content.

can be re-corrected / re-written as -

Please Login or Register  to view this content.

This way you do not have to remember which lrow variable points to which column.

Also, you have to qualify each line when you use the "With" statement. For e.g. if you are using range, it should be .range. If you are using cells and rows, it should be .cells and .rows, respectively. Check my code and you will understand.

[Seraph84]

Ctop, JosephP, thanks I will try that. I thought the With Sheets (xxxx) would have worked, guess not.

Arlu - I think you maybe mistunderstood my post (I also made an edit to it, sorry) - I still want to do a vlookup from the Rates sheet based on what is in the Source sheet. I see you removed reference to the Rates sheet? Can you perhaps explain what you did?

Also, the With statements seem to work fine with only Range (instead of .Range etc, though I understand what you are saying.) Also thanks for the tip about the lrow variable.

[arlu1201]

Since i already referred to the Rates sheet at the top of the code, i removed the repeated references to them. The changes that i did were - just adding the . where required and removing some un-wanted With and End With statements.

If you do not include the "." before the range, and by chance if your current sheet changes to something else, the actions will be performed on that sheet instead of the current sheet.

[JosephP]

Also, the With statements seem to work fine with only Range (instead of .Range etc, though I understand what you are saying.)

that's only if the right sheet happens to be active-you ought to always use the .Range notation

[Seraph84]

Sorry, I see where the confusion is coming in. The editing should be done on the Source sheet. And the vlookup and some minor editing is all that is required on the Rates sheet. I will edit the code in my post.

Apologies!

[Seraph84]

Ok I've modified my code as suggested and added the below:

Please Login or Register  to view this content.

But now I now I get Error 424 (Object Required) with this piece of code (I assume it would be the same for my other variables):

Please Login or Register  to view this content.

What am I doing wrong here?

[arlu1201]

Try this

Please Login or Register  to view this content.

[Seraph84]

Thanks Arlu, but i'm still getting the error?

[arlu1201]

It worked fine at my end.

Why dont you try with a dummy file and see if it works? I tried with one and it did.

[Seraph84]

Ok got it to work so everything on the Source sheet works as coded......but now my vlookup on the Rates sheet does not populate the range as specified. It only populates cells G1:2, instead of from G2 till the last row? And Column A is not deleted?

Please Login or Register  to view this content.

[JosephP]

remove the lrow12 part and use

Please Login or Register  to view this content.

[Seraph84]

Excellent! That seems to have worked, thanks so much everyone for the help!