Auto replace/update sheet

Hello Leith sir

Thank you so much for your help. I am not back to work until the Monday so will check it out and get back to you sir.

Much appreciated

Have a cool weekend

Ivor

Originally Posted by Ivor

Hello Leith sir

Thank you so much for your help. I am not back to work until the Monday so will check it out and get back to you sir.

Much appreciated

Have a cool weekend

Ivor

Good morning Leith

Hope you had a good weekend.

Thanks again for your help, your code worked without hitch, however I must admit I am totally lost with what you've said about the 'pasting to clip board'. Is this an easy step to do and will I have to remember it every time I'm wanting to code in a 'paste special'?

Also, as per the second part of the question, is there an easy bit of code I can wrap around yours, so that I get the option to overwrite the newly created sheet (work plan copy) if I ran the code again? currently I get an error message explaining that I cannot created a sheet that shares the same name?

Hope that makes sense.

Thanks
Ivor

Originally Posted by Leith Ross

Hello Ivor,

Here is the amended macro. If the sheet exists you will be asked if you want to overwrite the data. If the sheet name is not found then the sheet will be created.

Please Login or Register  to view this content.

Good afternoon Leith

Apologies for my late reply, been pretty crazy today with coding all over the place.

Thank you so much for your latest amendment to your code, the 'replacing' of the sheet works just fine as I get a messaged box as you coded. The only issue I am having is that although I get the option, changes to the original 'Work Plan' sheet are not being picked up on the 'Work Plan Copy' sheet. It worked the very first time when I ran your code, but has failed to work since!!! I wil try it again tomorrow but if there could be an issue with the way data is added to the 'Work Plan' sheet. Rows of data are often copied and inserted, before being overwritten (i.e. name changes and so on), however thinking about it the changes are being made before the 'copy and replace' macro is being run, and so it should work still. Maybe Excel is playing up today so wil give it a try again tomorrow.

Many thanks for your time Leith, much appreciated.

Speak soon

Ivor

Originally Posted by snb

Please Login or Register  to view this content.

WOW SNB

that was some quick work. Thank you for that. I feel bad as you are using your own time to help me and it is gratefully appreciated. Unfortunately I cannot use your code in it's current format, the reason being that I have another sheet which feeds directly from the 'Work Plan Copy' sheet (hence why I always need that sheet to be there). The very second I delete 'Work Plan Copy', all of my code on the linked sheet turns to #Ref. I have tried using .ClearContents instead of .Delete on your first line of code, but all this does is create a new sheet called 'Work Plan (2)'. What I ideally need is for your code to overwrite the existing 'Work Plan Copy' sheet instead of deleting it or creating a new one. Is this possible? Your code is already doing what I need in copying everything and is half of the size of the original code, that is really impressive sir.

Thanks
Ivor

Originally Posted by snb

If I get what you mean correctly this will suffice:

Please Login or Register  to view this content.

And if so, you could consider using a databasequery in worksheet("Work Plan Copy") in cell A1, that links to range("A1:AD100") in worksheet("Work Plan").

HA HA HA HA SNB you are a legend.

Why is it that sometimes the easiest way to achieve something works out to be the hardest task? Why was I trying to use some fancy macro to achieve what a simple "make one sheet = another" formula would do? I think I was initially thinking that the formula would be copied also, but luckily this is omitted and only the values are copied, so the rest of my coding should work fine. However I will not be too hard on myself for I have learnt a lot more than if I had arrived at this conclusion straight away.

Thanks again for your help SNB, and unless I come across any other issues (hopefully not) then I will tag this post as solved.

Your time has been invaluable sir

Thanks again

Ivor