• 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

Hello Sir
a) how many worksheets would the workbook contain?
It does not matter how many sheets as macro will go throught each and every sheet to perform the action.
b) how many rows (average) would each worksheet have?
It does not matter how many rows as well
c) how frequently are you going to add new worksheets or rows?
Jared Li is the right person to answer
d) how frequently are you going to emulate a Maneki-neko?
Jared Li is the right person to answer
 
SirJB7 said:
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!
Click to expand...

a) Worksheets: 20 approx
b) Rows: 1000 approx
c) It extracts data from an ERP software where Warehouse Order information changes very frequently.
d) no idea.
 
Hi, Monty!

And regarding your comments to the first 2 questions, even Jared Li is the right person to answer them, let me disagree with your answers.

It's a matter of philosophy, that can be expressed quoting Peter F. Drucker words "doing the things right or doing right things".

You can do things right (in this case the original macro posted by Jared Li and modified by you) and you will achieve the goal. But efficiently, in a short time, with a good performance? It's not the same for a book with 3 sheets of 100 records than for one with 50 sheets of 10K records each.

And even more if you consider my suggested approach where you'll be filtering and getting perhaps hundreds or thousands of non adjacent sets of rows with which you should operate on.

So before agreeing that my idea was so good as you kindly stated, I want to consider the answers to my 4 questions. Only then when Jared Li gives the actual or scheduled figures, I'd be able to maintain my suggestion or humbly retire it.

I always prefer to do the right things.

Regards!
 
Hi, Jared Li!
Thanks for your clarification.
Another thing: When you extract data from ERP, you update existing worksheets adding data at the end, you replace the data in those worksheets or you add new worksheets?
Regards!
 
Monty said:
Jared Li

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

Monty!
Click to expand...
Hi ,

Once your macro has run once , every cell in column C contains the following string :

ASDLO****

Can you not check if the first cell in the data range contains this string to prevent the copy and replace ?

Narayan
 
I am sorry for being late. I have attached a real nature of data that I am currently working with. Monty's code is good but if you could see the Column E in every sheet, it is not extracting the value of third parenthesis, it is copying the same values of Column C to Column E.

Sir JB7, i do create a new set of data, not replacing the earlier one.
 

Attachments

  • MEAT+SMALLGOODS FOR EDITING (2).XLSM
    87 KB · Views: 6
Oh that's the point, it copies the whole values for a column if it doesn't get the value it looks for. So it copies the whole value of Column C to Column D if it doesn't get any first parenthesis, so is for Column E. How can i get rid of that and shows the cell Blank if there is no value for a specific query.
 
You must log in or register to reply here.
Back
Top