Same macro to run in multiple worksheets

Dear Experts,

I have a set of data in three worksheets and the macro i m 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.

i posted this problem at https://www.excelforum.com/excel-pro...ml#post4631243 and Jindon gave me a solution as:

Sub test()
Columns(3).Copy Columns("b:c")
Columns(3).Replace "(*)", "", 2
Columns(3).Replace "[*]", "", 2
Columns(4).Replace "*(", "", 2
Columns(4).Replace ")*", "", 2
Columns(5).Replace "*[", "", 2
Columns(5).Replace "]*", "", 2
End Sub

But I can't make it happen for two other sheets at the same time. I wrote a code but its not working since I am novice and have very little idea about vb. I have not posted this problem in any other Forums. Thanks.

Thanks TMS but it extracted the whole thing to Column D and E. Column C has been refreshed and its okay but Column D has to pick only the values in Parenthesis (PERKG) and Column E has to pick only the values in Parenthesis [FISH]. Can you help me to get the solution? Thanks.

When I run the code it gives me :

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

But it 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

And it didn't change sheet 2 and Sheet 3 as well.

Can you help me to sort it?

I have cross posted this problem at http://forum.chandoo.org/threads/sam...ksheets.34102/ and looking for a solution very badly to save my job.

I have found a solution and given by Monty at Chandoo.org:

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