Move data to another area when cell value changes

[prabirmustafi]

I have this data



My question is if I change the value in cell A2 from Stage 1 to Stage 2 then the whole segment from cell A2:F2 should be moved to cell H11:M11 or if i change it to from Stage 1 to Stage 3 then it will move to O16:T16, similarly if i change Stage 2 in H4 for Kerr to Stage 1 then H4:M4 will be moved to cell A17:F17 immediately. Is it possible to do using VBA, and I have these type of blocks for multiple stages. Again, if i make changes in the last block like cell O15 Stage 3 for Huntington to Stage 2 it will move to cell H11:M11. The cell ranges in Column A, H and O have data validations of Stage 1, Stage 2 and stage 3, the names are dummy not real.

Is there any way of doing this using VBA? Any suggestions or links to understand the theoretical will help me.

 

[vletm]

prabirmustafi​

Here one sample version.
This take care Data Validations too.
Your sample data's COMP-columns has spaces in cells which looked empty - try to avoid that.
You skipped to write about those borders ... I coded my version for those.

 

[prabirmustafi]

@vletm sorry for the late response because I was trying on my own, wasn't able to make way.
regarding your supplied solution it is working with the sample data. But not with my original, I tried to adapt it but i am getting run time error 13.
And as you referred about the data validation, it does takes care of it when moved to other area but the existing cell doesn't show the validation anymore.

I have sanitized the private information. this is the original file copy. It has 7 area blocks and will increase in future, rows are now 1600, may increase in future. For now i am doing it manually every time, i have been asked to change the types in mentioned each blocks first column. Sorry I wasn't sure it will make so many differences. I am still trying to adapt your code, which is really helpful and working with sample data, but with the actual it gives me run time error. and it keeps coming up if click end, so i am closing the excel using task manager.

Can you advise what changes should i make?

 

[vletm]

prabirmustafi​

... You asked something but You thought something else
... You got something instead that something which You thought.
Even minor differences could make huge differences.
Did You get an idea?

Your My question is if I change the value in cell A2 from Stage 1 to Stage 2 then the whole segment...
Your Testversio-file has those Stage 1 ... Stage 3. ... which are missing from Your Email.xlsx-file.
That my file works with Your original rules.
... means that You should follow with Your original given rules/details or ...
Your newer file needs new clear rules.
I could have a new guess - how to do Your newer version?
... but it needs some 'minor' changes with layout.
Have You thought well ... why Your each block's the 1st column has to be there - each has same text?

 

[vletm]

Here next sample file with those minor modifications.
# INFO-sheet to select block
# Each block has own sheet
# Yellow cell shows 'where to move' (if different that current)
# If different than current block has selected from 'yellow' then
... select any cell from below 'header' to move to selected block
... ... there will be some questions ...
# If something has moved to other block then those rows can see right side of sheet (until next file opening)
# There can add 'as many block-sheets as needed' - ZZZZZ -sheet is blank sheet for that task.

>> I've tested only some basic steps with that ... there could be some interesting unwanted features <<​

 

[prabirmustafi]

@vletm this works nice. I will mark it as [SOLVED], but my actual file has lot of sheets, I cannot have these many sheets for the above question, I have to have only one sheet for emails, i dont want multiple, you have already given so much of time i don't want to waste your time, sorry i have to keep one sheet only with the same layout of blocks. I will do it manually for now. Thank you sooo much.

 

[vletm]

prabirmustafi​

Why do You can have only one sheet for emails?
... then this won't work at all based Your writings and thoughts!

 

[prabirmustafi]

prabirmustafi​

Why do You can have only one sheet for emails?
... then this won't work at all based Your writings and thoughts!

Yes agree with you. I have to think some other ways. But for now i will do it manually, takes lot of time though. Because the workbook has other informations sheet, like my first question sheet which was solved by you. and there aer 12 more sheets. all related.

 

[vletm]

Did You notice my the 1st question?
... anyway ... You skipped it.
Why Your 2nd sample file was again something else that Your original file?
( This is same as if You're talking about an apple but You're meaning a banana ... and after that You noticed that a pineapple ... mess! )
... if You could know/guess those other sheets names, then I could take care those...
Or
You should again delete that my 2nd sample file too - it won't work!

 

[prabirmustafi]

No my friend its okay. I dont want to waste your time. You have helped immensely. I will try to learn it and find a way to do it. Its not fair to keep you busy for my own office work.

 

[vletm]

I got an idea - how to do something for that 2nd version ...
... but it'll be again a pure guess without Your answers.
If I can solve this then this is something else than waste for me too.

... continue with 3rd version.
I added there two sample sheets A & B for testing purpose.
>> There is one button (left top corner to go back to INFO-sheet <<
... You should copy & paste it to Your those other sheets.
... It can be anywhere in sheet!
... after You've pasted one button to Your 'other'-sheets, You can delete those sheets A & B ... if You don't need those.
> INFO-sheet has other column for those Your ... 'other'-sheets.
... Those will be there automatic!

You can add to this file 'any number' sheets.

 

[prabirmustafi]

@vletm sorry for late reply. It is not what I am looking for. Has to be one sheet. Because those emails are used for sending emails. Based on instructions their category is changed.

 

[vletm]

prabirmustafi​

... hmm?
Those emails can us for sending emails
... what is someones challenge with that?
Those categories can change
... what is someones challenge with that?
This version shows ... one sheet in time ... as You wrote.

 

[prabirmustafi]

prabirmustafi​

Here one sample version.
This take care Data Validations too.
Your sample data's COMP-columns has spaces in cells which looked empty - try to avoid that.
You skipped to write about those borders ... I coded my version for those.

This was the right way. Sorry for the long delay. i am not concerned about the borders for now. that i can do manually or i can remove borders. but here it was three blocks originally i have 7 blocks for now. which may or may not increase in future. But the data is based on this https://chandoo.org/forum/threads/m...rea-when-cell-value-changes.56781/post-306579. Yes why it has to be one sheet because I copied one automail using excel vba from Ron De Bruins link post, which works for sending email automatically. There are 12 sheets each sheets has some other macros which have recorder. only the one i have requested for now will be instant change which i am not able to do. I am not asking to look into this further since you have helped me a lot. I really appreciate the time you have given hence why i dont want to waste your time. sorry

 

[vletm]

prabirmustafi​

You skipped all my previous questions ... why?
You wrote again that there could be more 'block' in the future ... my the latest version works.
Okay - You copy something somewhere and after that my version won't work with that paste anymore - that's normal.
Only sometimes copy & paste could solve something.
... recorded macros ... okay.
As I've written ... my the latest version could work with those Your 12 sheets too ... of course some modifications should do.
It's You, who seems to want to waste Your own time - not me.

 

[prabirmustafi]

@vletm sorry i haven't skipped any of your question. I am saying from the beginning that the layout will remain same, it will be in one sheet and want the changes to effect instantly when changing the dropdowns. lets forget about adding future blocks.but it cant be done using vba which instantly changes. I dont want multiple sheets.

 

[vletm]

Your i haven't skipped any of your question
... You've skipped to answer those too. (eg #13)
# Layout ... is still basically same.
# Dropdowns ... Your version has hundreds ... thousands of dropdowns ... mine version has so far much less ... much less.
# Future ... then You'll have a lot of new challenges there ... a lot.
# Basically, with my version You need to modify sheet's name to someone elses code.
# Your Want seems to be something else than Your need.

 

[prabirmustafi]

@vletm sorry for my delay in response. Can I send you a DM over here? is it legal to do ? So I can show my original data, as it has lot of emails which i cant share here.

 

[vletm]

prabirmustafi​

... two months later ... hmm?
If You could answer to those my questions, then it could be possible to continue.

 

[prabirmustafi]

@vletm i apologize for my delay. I had some family problem, for which i was not able to concentrate, just had to take a long leave from work . Now how i can explain what i was going through, please forgive me. sorry for the inconvenience