Copying data to another worksheet

This may seem to have a simple solution, but when I insert a name and date in worksheet 1 , I want the info to transfer automatically to worksheet 2 in the same formatted columns, but not be modified any more when I change anything in worksheet 1. For example, with dates, right now, I am using the formula =IF(H29>0%,TODAY()) the date will automatically change each day, but I just want it for the date it occurs then stay that way, not change every day to the current date, because that will also affect the date on worksheet 2. Is there a formula that will keep the original date on worksheet 2, rather than change to the current date each time?

Could you upload a sample sheet and i will have a look for you
galvinpaddy

See attached. The idea is when the test is completed, the date and name from the Sample Test will automatically transfer to the Sample Tracker Sheet. The data from there will then transfer automatically to the Sample Database. When you experiment with the Sample Test, you will see that as soon as you change or delete the name and date, it will also change or delete from the Sample Tracker and Sample Database sheets. I don't want the Tracker and Database info to be deleted once the test is complete, and the name deleted from Sample Test, will allow it be ready to be used for another person.
I could just use two workbooks, one for the tests, and for the tracker. database, but I am experimenting with it this way because the database does not need to have the info on tests completed. That is what the tracker is for. I will also be adding other data to the tracker that will not be required on the database.

Can anybody help me with this?

Hi, do you actually need to seperate sheets? can you combine all into one sheet and seperate using the tabs?

Hi again,

ok, having worked through what i think you are after, i have some results.
Please see attached 2 sheets:
Test
Test Tracker.
On the sheet TEST, please enter the name of the person taking the test, (the date is auto calculated) click the button ('Click to copy, save & restart')
This will transfer the data to the sheet named TEST TRACKER (you will need to open both sheets as the same time.)
The data will be pasted as values only, the coding is set to copy the info on TEST, paste values into TEST TRACKER, insert a new line (to always allow latest date to be at the top) the return to TEST and clear the cells containing names.
Please let me know if this is what you are after and we can then progress your request further by adding whatever is needed.

galvinpaddy.

Hi, galvinpaddy. thanks for the help. To start with your first response at 1206pm my intent was to have the three separate workbooks. I need the Test worksheet to be separate because the person doing the test does not need to see the test tracker or instructor list. The second sheet, Test Tracker is for my own recordkeeping so i know who has been given which test. The Database is for everybody to view so they can see when they last did the test and know that in 12 months will have to do it again.

Your assumption is correct how I would like to copy the info to the other worksheets. I am not familair with VBA. Test # completed is used to keep track of who was given what test.When I send the tests they do not have a number on them, so the member does not know which test he is getting and cannot simply keep the answers and transfer them to another person taking the test. I have 8 different tests created for this purpose. i normally have to manually record the name and test number in the Test Tracker-Instructor tab so i know which test I gave to whom. So when they do send me the completed test, I re-write the test number in the applicable column at the top of the page and the tab itself, and it will automatically transfer the test details to my Test Answer sheet (which I have already done) to give me the final results as a percentile. The member's particulars from the top of the Test worksheet and the percentage will then automatically transfer to the Test Tracker worksheet, and the particulars themselves would transfer to the Database, with the date it was completed.
I copied the Test and Test Tracker you modified onto my desktop, opened them up and inserted the info in the Test sheet, and it worked perfectly. This may seem like a simple equation to do, but you are awesome just the same. I will definitely have to learn more about using the macros and VBA. I tried looking at the macro or whatever it was you used for the transfer of data from the Test sheet to the Test Tracker, so could you humor on that and let me know where it is so I can know what to do. I will need to do the same rom the Test Tracker to the Database worksheet. Thanks.

ok, first, thanks for the thanks lol, you're welcome.
So, what steps do you want to add now? explain it to me in detail so i can add it for you.
Once its completed i will break it all down for you to easily understand, and will give you some pointers on how to start with VBA if you like, its a great tool to use but very difficult to master!

As for humouring you - you have excel 2007? first you need the developer tab (if you already hav eit then it will be next to the word View at the top of your excel screen) if you do not have it then - click excel icon top left corner of excel, click excel options towards the bottom left of the window that opens, then tick the tick box "Show developer tab in the ribbon" & then click ok.
Once you have it sorted, click 'Developer', click Visual Basic' and what you see opens up, is the code i wrote.
A good way to start learning VBA/macros - Click developer tab, click 'Record Macro' name it to what you want, then have a little play with excel, select a cell, copy it , select another, paste it, highlight a selection of cells. then click on the 'Stop Recording' button, click 'Macros' click once on the one you have just created, then select edit from the right side, and what you see there is the VBA code for the sequence you have just recorded in Excel!

Please let me know what you want adding to the sheet next and i will work on it tomorrow as its almost 2am in the UK and my little one will soon be up!
galvinpaddy

Thanks again. I will be studying and trying to understand this stuff as much as possible. As for the next step, I would need to copy the info from the Last Name, Fist Name and Dates in the Test Tracker sheet to a Database sheet, which I would like to have as a separate workbook as it will be for public viewing. I would like to have it so the names are reconfigured alphabetically rather than by date. I have tried a few methods on this, but they are not quite what I want. Once again, any help on this is greatly appreciated.
LordVankar

Hi,
I will have a crack at this for you tonight/tomorrow and post back, i had already created a sheet similar to what you want so i just need to find it & finish it!

To further elaborate on the Database, it would be set up so that each time the last name, first name and date are automatically transferred to the Test Tracker they are also sent automatically to the Database sheet.

Ok fella, no worries.
Leave it with me and if i have any questions i will let you know.
galvinpaddy

I also have an Answer sheet that automatically tells me if each answer on the Test sheet is correct or not and it gives me a percentage at the bottom. What I do with that is special copy it to a Test Results column on the Test Tracker sheet. This would also be done as each member completes a test. I had a system that worked, but it required manually inserting the formula for each of the eight different tests I could administer onto each line. For example, it would look like this for one row: ='[ANSWER SHEET.xls]AS #1'!$D$32/100. I used an IF function to copy each of these and change the AS # to reflect each of the tests and copied it onto each line in the Test Result column. But of course it created a long string.

Hi fella,
If you want me to incorporate that part also, i will need to see the sheet, as thats the only way i can actually ensure the code is correct.
I dont think it would be much extra work involved to get them all running together.

Ok. i have created a sample answer sheet for you. I have also included questions into the test sheet so you can see how that work,a dn two new spaces, INIT and DEPARTMENT. So, the first sheet opened is the Test worksheet, which will have the member's particulars and date of test.
Once the Test sheet is opened, the member's info will automatically transfer to the Answer Sheet, with the exception of the date. It will also calculate the results of the test from the Test sheet and transfer that to the Test Tracker on the Test Result column. Once that is done, the info on the Answer Sheet can be deleted, ready for the next member using the same test, and the Answer Sheet can be closed.
Also, on the Test sheet, a person can still press the button to copy the member's info to the Test Tracker sheet as you had already designed.
So now the info is on the Test Tracker, with number data corresponding from the Test # Completed column automatically copying over from the Instructors tab to the Test Tracker tab. THis is so I know how many of a paricular test has been given to what department.
I also want the data from the Test sheet to transfer automatically, or by the press of he button, to the Database worksheet.
All the workbooks/worksheets will be in the same folder. So all i have once i open the Test sheet is press the button and everything will automatically transfer to ther proper forms. I should not have to open any other worksheet to see if it transferred. But I can so anyway, just to be sure it worked. That is how I want it all to work.
I have tried a few times using the macro to modify the formula for the transfers, but I am still trying to understand some of the commands and equations and how they work, so no luck.

Awesome! It all worked perfectly. Imagine me shaking your hand right now. Thanks again. I will not need a password for the Test tracker and Answer Sheet right now, but I do know how to do that at least. Now it is just a matter of studying the equations so I understand better how it works. The parts I am having difficulty understanding are the Range ("I2:K2").Select, Range ("H2").Select for example. I have noticed that the 2 row is missing from the ANSWER and TEST worksheets which must mean it is only because of the equation being used. But why those columns and that row?

Hi, the code was written step by step, i write about 4 different macros separately, then combined them all into one.
Where you can see Application.Run "TEST.xls!answer_fill" that is telling excel to run the macro called answer_fill.
Row 2 is only hidden from view.
I2:K2 = select all cells from I2 across to K2 (which is on Row 2 that is hidden from view.) I have hidden the row from view as it contains the name, dept, init info but set out in the same layout that it needs to be pasted in the other sheets.
the part that says 'Range("H2").Select' is doing exactly that, selecting range H2.
There is no specific reasoning behind hiding row 2, thats just the row i had the data in it could have been in any row.
please enter lots of test data into the sheet and you will see what the code is doing on both the test tracker sheet and the database sheet.

I'm more than happy to answer any question you have, but please be aware, i dont have a great deal of experience with VBA so there may well be a way to condense my coding to make it easier to understand.

galvinpaddy

Hi there, the issue with the amount of questions is due to the fact i assumed you only had 10 questions, so i set the code to look in a specific range, this is the reason why i asked for data you work with
not to worry, shouldnt be to hard to sort for you, leave it with me and i will post it back asap.
*Changes Due* Please confirm these are correct!!!
1) 25 questions
2) calculation of %
3) remove the code that adds the info into the test tracker OR simply not delete the info you type into Column E? (TEST # COMPLETED)
4) is it possible to move the TEST # COMPLETED column to the end of the table? (so after the date column?)

If you can get me a response to the above soon i may be able to load the modified sheet tonight

galvinpaddy

Hi. It was originally for 10 questions, because I needed to make it easy for you to do. However, my actual test is 25 questions. It was easy enough to convert that but the percentages are giving me the problem. So to answer your questions:
1) Yes
2) Yes
3) Yes
4) Yes

Also, how does one access row 2 where the equations have been hidden? Or does it do it automatically based on what I insert for an equation in a particular row/column?

I have already figured out the test number so questions 3 and 4 are covered. I managed to figure out how to simply unhide the row, which would then show the equation in the highlighted sections you had and just added the equation for column J4. However, I am still working on the test percentage problem. But any help is still appreciated.

hi, ok.
First off, thanks for simplifying it to start with

Please see attached, these should cover the changes you mentioned and issues you encountered.
please let me know if you would like anything else

galvinpaddy

No problem. In fact, if you didn't have that revealed, it would have taken me a little longer to get the calculations to work, but now it all works they way I like. Thanks again.

Glad i could help!
Let me know if you have any more questions

As a matter of fact I do have another question. I am going to change my worksheet titles with the prefix IST, such as IST Test, etc. So I have made the changes to the worksheets and in the answer_fill, copy_paste and in macro 6 and 7 as well as the macros for Test Tracker and Answe Sheet, but I get the window error for the following line to end or debug:

Application.Run "IST TEST.xls!answer_fill"

How do I fix that? Do I need to change the answer_fill to another name?

hi, i wont have time to look at this tonight, i will do it tomorrow morning for you and repost
galvinpaddy

Just so you know I have tried to change it to istanswer_fill or ist answer_fill and no luck so far.

Roger that.

ok, morning fella.

I have now renamed all sheets and macros.
IST TEST
IST TEST TRACKER
IST DATABASE
IST ANSWER SHEET

I have also added an image (named changes) to begin to explain what you have to do if you want to change the work sheet names again.
The part that reads - Windows("IST TEST TRACKER.xls").Activate
If you change the work sheets to start with ABC, you would need to swap every single instance of IST with ABC, on EVERY macro module - you would need to have all sheets open to ensure you change them all, as one mistake can stop the whole process from running how it should and may produce different/wrong/no results

Let me know how you get on and if you need more help.
galvinpaddy

Ok. I figured out why I could not do anything once the titles changed. It was because when I was playing with the answer_fill label, the ' ' were missing from it. i also managed to figure out the equations and managed to get everything working the way I need. My next challenge is creating mutliple tests and a worksheet with multiple tabs for the answer sheets to match each of the tests. Should not be a problem creating them so much as ensuring they will link to the IST Test Tracker and IST Database sheets. More to follow on that.

Ok. This is great. I figured out why I could not do anything once the titles changed. It was because when I was playing with the answer_fill label, the ' ' were missing from it. i also managed to figure out the equations and managed to get everything working the way I need. My next challenge is creating mutliple tests and a worksheet with multiple tabs for the answer sheets to match each of the tests. Should not be a problem creating them so much as ensuring they will link to the IST Test Tracker and IST Database sheets. More to follow on that.

good good, if you need any help on something just shout fella and i'll help if i can

galvinpaddy

So far no luck with creating links to the multiple tests. What I want to do is a have several TEST sheets, say labeled , IST TEST 01, IST TEST 02, etc. In the ANSWER SHEET I would have the tabs labeled as TEST #1, TEST #2, etc. So what will happen is the questions from IST TEST 01 will link to the TEST #1 tab in the ANSWER SHEET to get the right answers, like I have now, but I need to do that for the other tests. The links would also work to the IST TEST TRACKER and IST DATABASE sheets where the data from any one of the TEST Sheets will automatically transfer to the TEST TRACKER abd DATABASE. What is the macro equation for that. I am still working on it, but still having trouble making a link just from the IST TEST sheet.

You cant do it yet because all of the macro codes are linked to another one at various and multiple stages.

With VBA codes, the best way to edit a code is to be the one who created it, especially in this scenario, because the best you can do is guess at what has been done
Send me a complete list of what you want - step by step. And how you want it to look when it is finished.
You will need to create all of the sheets and how you want each one to look, i will do what i can to start the code from scratch and create it in a manner that is both easier for you to read, understand & modify.
To make it easier for me, please give me all FINAL details - final sheet names/layouts/qty of questions etc etc.
I can send you my email if there is information present that you dont want available to the general public.

As i have already stated, i will do whatever i can to help, but you must be aware, i am no Excel guru lol so there may be things i cant do
galvinpaddy

Attached are the main four excel documents. For the IST TEST I have added a few extra rows for a member's particulars which has been calculated into the macro and it works. I have also included a Test number in J1, which is to remain hidden. The tab at the bottom of the sheet will just say Test.
For teh IST ANSWER Sheet, I have five Test tabs, each of them and their answers to match to the respective IST TEST questions. I did not number IST TEST as 01 yet, but that matches to the tab labeled Test #1, of course. I will send the other 4 IST TESTS I have created right away.

Here are the other 4 IST TESTS. My intent is to be able to send an IST TEST to a member by e-mail and receive it back by e-mail with his particulars and test answers on it. I will then save the test into the IST Folder with the other IST documents, open it, click on the button and the idea is for it to automatically detect the respective Test # tab in the IST ANSWER SHEET and calculate the results, which would then automatically go to the IST TEST TRACKER and the particulars and test date to the IST DATABASE, as already programmed.

Although, you say you may not be a guru, you have done very well so far in helping me out with this, so I have faith in you.

Thansk for that fella.
I have them all on my desktop so will get cracking! cant promise i will be able to do it all, but if not i will make sure you know
galvinpaddy

Hi Again, sry - Do you actually need the cell - INIT? i dont really need to know why, just need to know if it serves a purpose.

Sorry but the INIT does serve a purpose in muy line of work so I will need it. As for IST TESTS 2-5, I have found a few corrections that needed to be made, and what I did was any line that had IST TEST in it, I added the test number, such as Windows("IST TEST 02.xls").Activate. I also added a line for the IST ANSWER SHEET, such as Sheets("TEST #2").Select, below each IST ANSWER SHEET activate line. The results was that when I have all the excel windows open, I can use any one test and it will automatically calculate the data through the respective IST ANSWER sheet test tab and transfer the results to the IST TRACKER and IST DATABASE sheets. So that works so far. Now to try e-mailing it out and getting it back and trying to do the same wihout losing the data.
Sorry if I am already getting ahead of you on this when I asked for help, but I am sure I will still need some assistance with this. As you can see I am slowly but surely getting used to how the equations work in macros. the amended sheets are attached.

Hi, perhaps you could simply email out the generic sheet (attached) one with NO coding, then have all the code on the Answer sheet, so you would open the sheet they send you back and the answer sheet, click the button ans the code sorts the rest.
Would this work for you?

I just want to confirm you are saying that the coding would be on the answer sheet with the button to do the transfer. So open the Answer Sheet and Test sheet being used, press the button on the answer sheet and it calculates. That would work for me. If there is another method without pressing a button but once the test sheet appears in the same folder as the Answer Sheet it would still calculate and transfer automatically, that would also work for me.

Well I did manage to conver the button to the Answer Sheets, and it did work. However, whenever I try to send the test to myself, insert the answers and send it back, most of the time I cannot transfer the test data, and I end up having to create that test all over again, because it seems to have lost its link. I have also tried copying the files from one computer to another, but that was unsuccessful as the files seem to be missing the link to the copy_paste functions at least. Another issue I am trying to do is set up the IST DATABASE so it will automatically transfer the text to UPPER CASE. I have looked it up in the forum, but I am trying to figure out how to apply that formula to the macros.
Anyone have any ideas for any of my issues?

No problem on taking care of the real life issues. Glad to hear form you again. I have been doing a lot of experimenting and had it working for the most part. I will get to the upper case function soon, but my main issue right now is when I send a copy of the excel test to myself, fill in the answers and send it back, sometimes it works when I press the button on the ANSWER SHEET and most times does not work. I end up having to copy the test questions over again on that test and then it works again. Why is that? I haven't been able to figure it out yet. It is the one thing keeping me from completing this project. I think the other issue with that is if I try to save a copy of the test in another folder but within that folder, the test is just labeled as IST TEST, it does not work, even when I save it back to its original excel worksheet and title. Any suggestions for that problem?

Unfortunately, the allcpas formula you provided did not work, but I did find one that did,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 26 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

So I have that working, as well as auto enable for my multiple workbooks and now I am working on trying to automatically send an e-mail message when a person reaches a certain percentage on the test. If they pass, they get a pass message. if they fail, they get a fail message. I am inserting that into the IST TEST TRACKER sheet.