do you just want to make a copy of the whole sheet, or only specific columns
it certainly is possible, but as far as updating the 2nd sheet when changes are made to the first, it is easier just to delete the old sheet and copy again, that would be much faster then making individual changes.
Now for the cost, I cannot possibly write this code for what you are offering. I am sending you an offer for additional service, If you choose to accept it then I can write the code for you.
i'll refer your question
I have an answer for you. Please let me know if there are errors because I did not understand your question or I missed a requirement. Because this file uses VBA code and needs to be a macro file (.xlsm) I cannot directly load it to the question. The file is available on mediafire (no cost, just click the link I provide) here: http://www.mediafire.com/view/xurbx3x6s97r19u/autocopy.xlsm
I made the Worksheet_Change call a function I wrote that takes two arguments, the orig and dest. Changing the text in the Worksheet_Change sub procedure will allow you to change what the names are ***** ***** source and destination. You can copy and paste those two sub procedures into any worksheets you want copies of on update. Please ask questions if you have any.
It is blank. Type something into Sheet1, then look at Backlog. Backlog won't be blank anymore. I used VBA code, which is a macro, but you won't see it unless you open it. For what you want to do you need to use event triggers, which are accessible only in VBA.
Also, you need to enable the macros.
Make sure you download the file too, some web browsers try to display a print preview that you cannot interact with when you follow the link through mediafire. You can click the link and click the download button on the top right center above the preview or right-click and select save as (if your browser allows that).
What version of excel are you using (so I can tell you the correct ribbons/toolbars)?
The VBA code viewer is on the developer tab. To display the Developer Tab go to File, then Select Options from the drop down menu. Then click Customize Ribbon, on the right third of the screen you will see where you can select the ribbon, click the check box next to Developer. Now when you go back to Excel you will have a Developer tab, click on that. Left-most icon says Visual Basic, click on that. It will open the VBA code viewer, make sure you have Sheet1 selected to view the code associated with that sheet. It's really only a few lines.
You would need to copy and paste the vba code into the worksheet you want to use as the source to copy from. The reason is that you want the change event for that worksheet to execute the copy. Then just check the worksheet names against VBA, but I used the names you provided so it shouldn't be an issue for the names. Just copy and paste.
I have broken the copy and paste into two statements, that way I could use the pastespecial command, which allows me to specify to copy all items. The shortcoming of excel is that change only detects value changes. So if you only changed formatting excel won't trigger to copy. However, if you type something then delete it and press enter it triggers the change event. The updated solution is here: http://www.mediafire.com/view/xaoz846t1x8eqxa/autocopy_rev01.xlsm
It's a limitation of excel. Change fires anytime you change what is in a cell (a formula, text, date, hyperlink) anything but the cell's formatting. I tried another solution, where I added the copy sub procedure to the Backlog sheet. All I changed was the event trigger, so when you activate Backlog (select it), it will execute the copy all function. With excel's limited trigger events it's the best bridge I can make to ensure that formatting changes get copied if that's all you change, that way there is no button to add or that you have to edit a cell just to make sure the copy happens (which is an option, but in my opinion a very dirty one). I would probably check the backup anyways, just to be sure so this should work for you: http://www.mediafire.com/view/332c2rns152lwxb/autocopy_rev02.xlsm
Let me know which works better for you, like I typed only difference is the extra trigger when you view Backlog. This is why I use revision numbers in my filenames, so I can move between copies and retain the old ones.
Please let me know what else needs to happen to answer your question. When you feel it has been answered please rate my service to you. Thank you.
It sounds like a memory limitation. Remember excel is not a database optimized for a lot of data storage. What may seem like a little to you is a lot for excel. I would disable the VBA code,just remark the copy command call in the event trigger, paste your data, then remove the remark from the copy command to re-enable. When you copy you are updating multiple times. My machine runs 8 threads at once on a 3GHz core with 24 GB of memory. What you are attempting to do is exceeding your hardware's capability. Excel natively has memory leaks, no one can do anything about that (except Microsoft, and their answer is to buy a newer version). Controlling and limiting the number of copy operations is really the only way. I have a feeling Excel is not completely thread safe, which is a limitation on no one can control. Remarking the commands while you copy and paste is one option. Copying and pasting a few rows at a time in another method. I cannot reduce the load on your machine from a copy and paste with those event triggers, realize this is optimized for human one at a time change, the only way to optimize for mass change would be to copy on calculate, which would not work in most cases for you, and in this case would make it worse.
Any solution is a targeted trade-off that reduces capabilities in other operating conditions, which is why I would recommend remarking while you mass copy and paste. It is simpler for Excel to handle.
It is probably too much on the clipboard at one time. I'll need to rewrite the copy and paste function to perform the operation in a series of small chunks. I can have it by Monday afternoon.
In this revision I used chunking to grab pieces of the sheet at a time. However, with the pastespecial to get all of the data, formulas and formatting, the problem tries to update inside itself when Backlog has the Activate event trigger enabled to perform the copy. I had to remove that, but updates should not fail now. Let me know how this works: http://www.mediafire.com/view/ehy5dj6vkxys452/autocopy_rev03.xlsm
What formatting was not being maintained? I would like to check on my machine so I can see. Can you tell me the formatting above and below, like a one cell example, so I can add the new row and see what it does? That way I can try it exactly with the problem you are having.
Okay, let me look into that.
Did you enter any data after you made the formatting change?
I added a module to put the copy function there. I also found the command to stop looping by disabling event triggers and then re-enabling, so it copies a little more cleanly and more quickly when there is a lot of data present. Also, I put the Activate trigger back into Backlog to call the copy command, so that will take care of the formatting. Here is the download link:http://www.mediafire.com/view/enyp2vjbdgsrjn1/autocopy_rev04.xlsm
Let me know if that works now.
So paste special paste all does copy and paste formatting, but apparently Microsoft does not regard row height and column width as formatting (oddly everyone who uses Excel does). So I added two for loops to loop through and do the assignments. Try this: http://www.mediafire.com/view/25ru639yv33zf5r/autocopy_rev05.xlsm
I will not have internet access until Thursday evening, so try it out and let me know what else it needs. This should be everything.