• 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...

  • 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

J

Jared Li

New Member
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.
 

Attachments

  • TESTDATA (1).xlsm
    20.7 KB · Views: 3
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!
 
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: 
Sub test()
dim ws as worksheet

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

next ws
End sub
 
Small change!

Code: 
Sub test()
dim ws as worksheet

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

next ws
End sub
 
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
 
Try this!

Code: 
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
 
If you want avoid any particular sheet where you do not want to run the macro please check below code.

Code: 
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!
 
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!
 
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?
 
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!
 

Attachments

  • TESTDATA.xlsm
    19.5 KB · Views: 8
Well I am a novice here and looking for your expert solution. I am attaching the file again if you can open it.
 

Attachments

  • Sample Data.xlsx
    10.2 KB · Views: 4
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.
 
Hey

Try this...Just click on the button.

Am sure your boss must be happy now!
 

Attachments

  • TESTDATA.xlsm
    28.2 KB · Views: 12
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!!
 
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
 
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!
 
You must log in or register to reply here.
Back
Top