How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Zabo04 Your Own Question

Zabo04
Zabo04, Programming Enthusiast
Category: Programming
Satisfied Customers: 282
Experience:  C/C++, python, perl, excel, matlab/octave, java, and R experience. Genetic algorithm, vision, GUIs.
58597962
Type Your Programming Question Here...
Zabo04 is online now
A new question is answered every 9 seconds

What is the code to copy the contents of an excel worksheet

Customer Question

what is the code to copy the contents of an excel worksheet named "sheet1" to another existing worksheet named "backlog" automatically using VBA
Submitted: 1 year ago.
Category: Programming
Expert:  The-PC-Guy replied 1 year ago.

do you just want to make a copy of the whole sheet, or only specific columns

Customer: replied 1 year ago.
The whole sheet, but, I want it to make changes to the secondary sheet automatically when information changes on the original.
Expert:  The-PC-Guy replied 1 year ago.

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.

Customer: replied 1 year ago.
Refund my original payment!
Expert:  The-PC-Guy replied 1 year ago.

i'll refer your question

Expert:  Zabo04 replied 1 year ago.

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

Expert:  Zabo04 replied 1 year ago.

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.

Customer: replied 1 year ago.
All I see is a blank spreadsheet with no macros
Expert:  Zabo04 replied 1 year ago.

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.

Expert:  Zabo04 replied 1 year ago.

Also, you need to enable the macros.

Expert:  Zabo04 replied 1 year ago.

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).

Customer: replied 1 year ago.
It seems to work, how can I see the VBA code?
Expert:  Zabo04 replied 1 year ago.

What version of excel are you using (so I can tell you the correct ribbons/toolbars)?

Customer: replied 1 year ago.
2013
Expert:  Zabo04 replied 1 year ago.

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.

Customer: replied 1 year ago.
if I want to insert this into an existing spreadsheet is there anything special I need to do?
Expert:  Zabo04 replied 1 year ago.

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.

Customer: replied 1 year ago.
That seemed to work, one other question which I should have probably mentioned earlier - I want to maintain and copy all of the formatting (color's, hyperlinks, etc) from the original worksheet to the new one.
Expert:  Zabo04 replied 1 year ago.

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

Expert:  Zabo04 replied 1 year ago.

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

Expert:  Zabo04 replied 1 year ago.

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.

Expert:  Zabo04 replied 1 year ago.

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.

Customer: replied 1 year ago.
When I paste my data into version two - Xcel has a fit for about 10 seconds and shuts down.
Expert:  Zabo04 replied 1 year ago.

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.

Expert:  Zabo04 replied 1 year ago.

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.

Customer: replied 1 year ago.
I've disabled the VBA Code and it seems to be fine up until I re-enable it and make any change - Then the same thing happens. Thoughts??
Expert:  Zabo04 replied 1 year ago.

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.

Expert:  Zabo04 replied 1 year ago.

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

Customer: replied 1 year ago.
After formatting the backlog sheet and a few tweaks it appears to be working. I will do some further testing and let you know.
Customer: replied 1 year ago.
Update - When I cut and pasted data into rev 3 it copied all the info and maintained all the formatting over to the backlog page. However, when I add a new row, it copies the new data, but does not maintain the formatting when copying it to the backlog sheet. Thoughts?
Expert:  Zabo04 replied 1 year ago.

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.

Customer: replied 1 year ago.
I inserted a blank line below my column headings at the top of the page and it will only copy the entered text. No cell color or hyperlinks. When I originally copied the the data into sheet 1 from an existing spreadsheet it copied all the formatting from sheet 1 to the backlog sheet with the exception of the column width formatting.
Expert:  Zabo04 replied 1 year ago.

Okay, let me look into that.

Expert:  Zabo04 replied 1 year ago.

Did you enter any data after you made the formatting change?

Expert:  Zabo04 replied 1 year ago.

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

Expert:  Zabo04 replied 1 year ago.

Let me know if that works now.

Customer: replied 1 year ago.
Color and hyper links now copy over and work fine. Column and row height changes still don't copy across.
Expert:  Zabo04 replied 1 year ago.

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.