+ Reply to Thread
Results 1 to 6 of 6

Macro works and loops perfectly in VBA but does not loop on button click

  1. #1
    Registered User
    Join Date
    11-21-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Macro works and loops perfectly in VBA but does not loop on button click

    Hi All

    I'm completely stumped here. I've written the following macro for a Scorecard workbook I'm creating. The basics of what it's meant to do are:

    * have one workbook to collate data from various other workbooks
    * main workbook (called Launchpad), has a "Data" tab, which lists all of the file names from which to pull data
    * on click of a button, the macro starts
    * the first thing it does is go to the "Master" tab, and clear any info from the table (so that it gets refreshed)
    * it then opens the first workbook from cell A1 in the Data tab
    * in the now open workbook it goes to a designated cell, filters out the blanks, selects all data in the table and copies it
    * it then goes to the "Master" tab in the Launchpad workbook and pastes the data
    * it then returns to the other workbook, unfilters the table (putting the blanks back in), saves and closes that workbook
    * lastly it returns again to Launchpad to the Data tab and moves down one cell to the next file name
    * a loop then starts where the workbook opens the next file, filters, copies, returns, pastes etc.
    * this is meant to occur in a loop until a blank cell is reached on the Data tab

    Now, the issue I'm having is:
    * if I run the macro from VBA, it works one time, going through the loop as intended. The next time I run it, it seems to only go once, stopping after the first run of code (before the loop starts). If I run again, it works. Every second time ...
    * if I run the macro from the button click, as intended, it also runs through once, stopping before the loop (i.e. only one workbook's data is grabbed). It never loops from the button.

    The code is:

    Please Login or Register  to view this content.
    Any help would be much appreciated. Thanks guys!!!
    Last edited by jeffreybrown; 11-21-2012 at 11:50 PM. Reason: As per the forum rules, please use code tags...Thanks.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro works and loops perfectly in VBA but does not loop on button click

    Hi,

    Try "Next X" instead of simply just "Next" and see what happens.

    The other suggestion is to put some "DoEvents" in your code to ensure it isn't a timing problem.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro works and loops perfectly in VBA but does not loop on button click

    Try this...

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-21-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Macro works and loops perfectly in VBA but does not loop on button click

    Thanks for the replies!!

    Changing to Next x has the same result. It works from VBA but not from clicking the button.

    I also tried AlphaFrog's code (MUCH more succint than my mess!!!). It only pastes the last workbooks data, ignoring any of the previous ones.

    Any idea why clicking the button on my code would disable a loop?

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro works and loops perfectly in VBA but does not loop on button click

    Quote Originally Posted by evan.sams View Post
    I also tried AlphaFrog's code (MUCH more succint than my mess!!!). It only pastes the last workbooks data, ignoring any of the previous ones.
    Try changing this...
    Please Login or Register  to view this content.

    To this...
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-21-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Macro works and loops perfectly in VBA but does not loop on button click

    Ahhhhhhhhhhhhhhhhhhhhhhhhhhhhh it works!!!!!!!!!

    How f**king awesome are you?!?!?!?!?!?!

    Thank you!!!!!!

+ 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