Macro is not looping correctly through all sheets

[dagindi]

When I run the following macro on a workbook with more than one worksheet, the first worksheet formats perfectly but then each subsequent worksheet does everything up to the last vlookup.

The formulas/info in cells B, D, E, & G do not copy down.

I know it has something to do with the last row code, but I cannot figure out what it is.

Ive hashed out some other code I was working on. This wasnt working either but I think once the first problem is address I will be able to fix the rest of the code.

Please Login or Register  to view this content.

Thanks!

[JBeaucaire]

In your LASTROW code, you forgot to add the leading dots which connect that command to the With ws command given previously:

Please Login or Register  to view this content.

[dagindi]

JB,

Thanks for the reply!

At first I was going to cry based on your response but after making the code change I am still getting the same result. Sheet one perfect, all subsequent sheets get everything up until the LR coding.

Maybe this will help:

I reran the macro step by step and got the same results.

The I ran the macro step by step and at each "Next ws" i manually switched to the next worksheet and it worked.

If i continued step by step without switching to the next page then i wouldn't populate the data down.

[JBeaucaire]

I looked again and found MORE missing dots...look at the part AFTER the = in these lines:

Please Login or Register  to view this content.

[dagindi]

Made the changes and still a no go.

Then I add the dots to everything as follows:

Please Login or Register  to view this content.

Before I ran the macro again I ran a clear excess formats add-in i got yesterday and now everything works.

Question is, was it the dots or the clearing the excess format or both??

[dagindi]

JB,

As I continue with writing the code for this macro I keep finding that once I hit a line of similar to the following the process will revert back to the first worksheet and mess the data up.

I made notes as to which lines work and where I think the issue is that causing the loop to not work correctly.

Please Login or Register  to view this content.

[JBeaucaire]

Why are you selecting things? You cannot SELECT anything on a sheet that is not activated. Activating is inefficient and unnecessary usually.

If you're trying to delete all the data from row2 down, try this:

Please Login or Register  to view this content.

[JBeaucaire]

Better yet, perhaps you just "spot" the last row with visible data and delete specifically:

Please Login or Register  to view this content.

[dagindi]

The latest bit of code you provided worked!! Thank you very much

The reason why I was using select is because, sadly, I still piece my macros together from other codes hoping that what worked before will work again.

Unfortunately I find myself trying to do things beyond my knowledge.

Many thanks!!

[JBeaucaire]

I use the macro recorder frequently to get some idea of basic code, but that always has to be edited to take out the "humanistic" approach it records and deal with the data directly. Cheers.

[dagindi]

I've been doing the same thing but im still figuring out how to chop it down. Plus the macro recorder would have never taught me "usedrange"

[JBeaucaire]

Heh, no worries, as all edit down our recorded code into efficient VBA, we learn tricks and pass them on. Cheers.