Consulting

Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 61 to 80 of 84

Thread: VBA - Search For Value Across Multiple Worksheets

  1. #61
    Paulked,

    So I tried getting rid of all the formulas and set it to Manual and it was running for 2 hours and then Excel was "Not Responding". Any ideas what else I can do? It runs on the test workbook that I sent to you. But with the larger workbook it doesn't execute.

    Thanks.
    Steve

  2. #62
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    I've no idea sorry, I've never seen your workbook... despite asking several times.
    Semper in excretia sumus; solum profundum variat.

  3. #63
    Paulked,

    The workbook is 35 Mega Bites and it is too large to attach here. That is why I sent you the test version, which is an exact replica with fewer worksheets.

    Any other way I can get the file to you?

    Regards,
    Steve

  4. #64
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,197
    Location
    Quote Originally Posted by Steve Belsch View Post
    Paulked, The workbook is 35 Mega Bites ...
    That could be the reason it runs so slowly ... it might be time to re-think the approach and re-architect the workbook
    ---------------------------------------------------------------------------------------------------------------------

    Paul

    Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #65
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    That could hold the Vatican Library!!

    Try saving is as an xlsb file, see what that does to the file size?

    And get rid of ALL the formatting. I'm guessing you have filled the worksheets with colours... the whole of the sheets.
    Semper in excretia sumus; solum profundum variat.

  6. #66
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    Semper in excretia sumus; solum profundum variat.

  7. #67
    Paul (Can I call you Paul),

    But if I save it as a .xlsb file .... will that not run the Macro that you have helped me build?

    Thanks.
    Steve

  8. #68
    Paul,

    I shut everything program down and the waited. Created a Macro to put every tab in Values. The CPU was almost at 100% the entire time but it worked!

    Thank you so much for your help!!! You are my go to as I continue to learn VBA.

    Steve

  9. #69
    Ah!!! Actually, i worked in terms of creating the list of used PO's but it did not delete the rows of the "Accrual & PO Data" worksheet. I don't get it. It worked on the test workbook. Ah!

  10. #70
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    You can call me anything!

    Do you think I would ask you to save it as a binary file if it wouldn't run a Macro?

    You could save the file to a shared location (OneDrive, Dropbox, GoogleDrive etc) and share the link. At least I would have chance of finding the problem.
    Semper in excretia sumus; solum profundum variat.

  11. #71
    Paul,

    I just saved as a binary file as you suggested. let me try to save it in that location.

    Thanks,
    Steve

  12. #72
    Paul,

    I just saved as a binary file as you suggested. let me try to save it in that location.

    Thanks,
    Steve

  13. #73
    Paul,

    I have saved it to Drop Box. How do I send you a link?

    Thanks.
    Steve

  14. #74

  15. #75

  16. #76
    Paul,

    Any more ideas on why this gets hung up. Even if you check out my "Vlaues" macro that should reduce time, but it just dose note even delete PO's.

    Thanks,
    Steve

  17. #77
    Paulked,

    Are you able to see my full file?

    Thanks,
    Steve

  18. #78
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    As I understand it, this is what you want to do:

    1. Get all the PO's off the Accrual & PO Data sheet
    2. Loop through all sheets
    3. If the sheets are in the named list, don't do anything (skip them)
    4. If the sheets are not in the named list, delete all the PO's that are in the Accrual & PO Data sheet off that sheet

    Is that absolutely correct? Read it three times!!!
    Semper in excretia sumus; solum profundum variat.

  19. #79
    Paul,

    Yes. That is it.

    can you see the file?

    thanks,
    steve

  20. #80
    Paul,

    Any idea why it is so long to load?

    thanks,
    steve

Posting Permissions

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