1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Same macro to run in multiple worksheets

Discussion in 'VBA Macros' started by Jared Li, Apr 21, 2017.

  1. Jared Li

    Jared Li New Member

    Messages:
    20
    Dear Experts,

    I have a set of data in three worksheets and the macro i am using should run in three worksheets at a time from a single macro button in Sheet 1.

    The data that I have needs to be extracted from Column 3 and separate the values in () in Column 4 and the values in [] in column 5 and at the same time Column 3 needs to be refreshed and shown without the separated values and that should happen in every worksheets at the same time.

    The expected result in Sheet1 should be:

    PO Number CUSTOMER NAME ORDER DETAILS () [] QUANTITY INSPECTED
    139778 ATLAS ASDLO**** PERKG FISH 23 YES
    139779 NANDOS ASDLO**** PERKTN MEAT 55 NO
    139780 ATTRONS ASDLO**** PERKG POULTRY 44 YES
    139781 VIDVEST ASDLO**** PERKG FISH 33 NO
    139782 LORENZO ASDLO**** PERKG FISH 22 YES

    So would other worksheets be like this as well.

    I have cross posted this at:
    https://www.excelforum.com/excel-pr...o-run-in-multiple-worksheets.html#post4634337
    But haven't been able to solve it. I have attached the file here so that You can see it and give me a solution. Thank You All.

    Attached Files:

  2. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Jared Li!

    Thanks for the notice about crossposting, please update both threads when you find a solution.

    First of all, a block "With ws ... End With" is missing into the "For... Next" loop.
    Next, range Replace method doesn't accept wildcards so you should change your approach.

    Regards!
    Jared Li likes this.
  3. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello Jared Li

    Could not get a chance to look at your code as unable to download the file..but what i can understand is you wanted to run the same macro in the three sheets.

    Try this.
    Code (vb):
    Sub test()
    dim ws as worksheet

    For each ws in workbooks.sheets
    < Your code goes here>

    next ws
    End sub
    Jared Li likes this.
  4. Monty

    Monty Well-Known Member

    Messages:
    833
    Small change!

    Code (vb):
    Sub test()
    dim ws as worksheet

    For each ws in Activeworkbooks.sheets
    < Your code goes here>

    next ws
    End sub
    Jared Li likes this.
  5. Jared Li

    Jared Li New Member

    Messages:
    20
    Thanks Monty, its not working unfortunately. I am posting my code here so that you can quickly look at the problem:

    Sub test()
    Dim ws As Worksheet
    For Each ws In Activeworkbooks.Sheets
    .Columns(3).Copy Columns("d:e")
    .Columns(3).Replace "(*)", "", 2
    .Columns(3).Replace "[*]", "", 2
    .Columns(4).Replace "*(", "", 2
    .Columns(4).Replace ")*", "", 2
    .Columns(5).Replace "*[", "", 2
    .Columns(5).Replace "]*", "", 2
    Next ws
    End Sub
  6. Monty

    Monty Well-Known Member

    Messages:
    833
    Try this!

    Code (vb):
    Sub test()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
    ActiveSheet.Columns(3).Copy Columns("d:e")
    ActiveSheet.Columns(3).Replace "(*)", "", 2
    ActiveSheet.Columns(3).Replace "[*]", "", 2
    ActiveSheet.Columns(4).Replace "*(", "", 2
    ActiveSheet.Columns(4).Replace ")*", "", 2
    ActiveSheet.Columns(5).Replace "*[", "", 2
    ActiveSheet.Columns(5).Replace "]*", "", 2
    Next ws
    End Sub
     
    Jared Li likes this.
  7. Monty

    Monty Well-Known Member

    Messages:
    833
    If you want avoid any particular sheet where you do not want to run the macro please check below code.

    Code (vb):
    Sub test()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets

    If ws.Name <> "Mainsheet" Then

            ActiveSheet.Columns(3).Copy Columns("d:e")
            ActiveSheet.Columns(3).Replace "(*)", "", 2
            ActiveSheet.Columns(3).Replace "[*]", "", 2
            ActiveSheet.Columns(4).Replace "*(", "", 2
            ActiveSheet.Columns(4).Replace ")*", "", 2
            ActiveSheet.Columns(5).Replace "*[", "", 2
            ActiveSheet.Columns(5).Replace "]*", "", 2
    End If
    Next ws
    End Sub
     
    Still any challenges..let me know happy to help you.

    Monty!
    Jared Li likes this.
  8. Monty

    Monty Well-Known Member

    Messages:
    833
    Just to remind you...this code start working in all the sheets available in your activeworkbook expect sheet name called ""Mainsheet" as an example.

    Monty!
    Jared Li likes this.
  9. Jared Li

    Jared Li New Member

    Messages:
    20
    Monty it is just taking off the values inside the parentheses in column C and copying the same to column D and E and unfortunately not changing anything in sheet 2 and sheet 3.

    All I need is take off the values inside first parenthesis () in column D and third parenthesis [] in column C and show Column C without the separated values which is okay now and do the same to other worksheets. Can it be done?
  10. Monty

    Monty Well-Known Member

    Messages:
    833
    Jared Li

    I really don't know what your macro does...just concentrated on how to run the same macro in all sheets!

    Monty!
    Jared Li likes this.
  11. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello.

    Finally able to download your file..
    I think no macro is required for this..i just done with a simple formula...check if it works.


    Monty!

    Attached Files:

    Jared Li likes this.
  12. Jared Li

    Jared Li New Member

    Messages:
    20
    Well I am a novice here and looking for your expert solution. I am attaching the file again if you can open it.

    Attached Files:

  13. Jared Li

    Jared Li New Member

    Messages:
    20
    There would be thousands of rows and many worksheets full of complicated order information so what i need is to cut the information down to minimum. And it is quite impossible to go through every sheet and edit the order information. i was using this formula but my boss wanted me to get some kind of trick so that it is done automatically.
  14. Monty

    Monty Well-Known Member

    Messages:
    833
    Hey

    Try this...Just click on the button.

    Am sure your boss must be happy now!

    Attached Files:

    Jared Li likes this.
  15. Jared Li

    Jared Li New Member

    Messages:
    20
    Thats awesome Monty, u rock. One last thing, if somebody clicks on the button twice, it would be a disastrous. It just copies the whole values in Column C twice to other sheets. can that be checked? I am happy enough with the solution though. thanks mate. Cheers!!
  16. Monty

    Monty Well-Known Member

    Messages:
    833
    Jared Li

    That usually happens with any macro...when you run for the second time...So need to ensure.

    Monty!
    Jared Li likes this.
  17. Jared Li

    Jared Li New Member

    Messages:
    20
    Thanks mate. You've made my life easier. Cheers!
    Monty likes this.
  18. Monty

    Monty Well-Known Member

    Messages:
    833
    Jared Li

    But quite interesting question how to stop somebody to click only onces?
    Jared Li likes this.
  19. Jared Li

    Jared Li New Member

    Messages:
    20
    Yeah mate :D
  20. Monty

    Monty Well-Known Member

    Messages:
    833
    Am happy that it was helpful.

    Please let me know any future challenges...Happy to help you!
    Jared Li likes this.
  21. Jared Li

    Jared Li New Member

    Messages:
    20
    Thanks Monty. You've been really helpful :).
  22. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Monty, Jared Li!
    A suggestion without any intend to get my hands dirty on this.
    Why not filtering first column C containing "(" and then applying the actual process to the filtered rows? Then you'd be able to put a Maneki-neko near the keyboard and get the button clicked the whole day without any issue.
    Regards!

    PS: Just in case...
    https://en.wikipedia.org/wiki/Maneki-neko
  23. Monty

    Monty Well-Known Member

    Messages:
    833
    Sir.

    No words for your expertise..
    Really a good one!
    Monty!
  24. Jared Li

    Jared Li New Member

    Messages:
    20
    Thanks, haven't got that though.
  25. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Monty, Jared Li!
    Thank to both of you but before agreeing with you I'd like to know, Jared Li:
    a) how many worksheets would the workbook contain?
    b) how many rows (average) would each worksheet have?
    c) how frequently are you going to add new worksheets or rows?
    d) how frequently are you going to emulate a Maneki-neko?
    Maybe after these clarifications I'd return to my still dumb behavior.
    Regards!

Share This Page