+ Reply to Thread
Results 1 to 12 of 12

Macro is not looping correctly through all sheets

  1. #1
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Macro is not looping correctly through all sheets

    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!
    Last edited by dagindi; 03-24-2011 at 05:04 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro is not looping correctly through all sheets

    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.

  3. #3
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Re: Macro is not looping correctly through all sheets

    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.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro is not looping correctly through all sheets

    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.

  5. #5
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Re: Macro is not looping correctly through all sheets

    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??

  6. #6
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Re: Macro is not looping correctly through all sheets

    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.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro is not looping correctly through all sheets

    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.

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro is not looping correctly through all sheets

    Better yet, perhaps you just "spot" the last row with visible data and delete specifically:
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Re: Macro is not looping correctly through all sheets

    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!!

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro is not looping correctly through all sheets

    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.

  11. #11
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Re: Macro is not looping correctly through all sheets

    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"

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro is not looping correctly through all sheets

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1