Transfer data and clear cells??

[Chris Watson]

I have 5 cells and a SAVE button on sheet 1, I also have 5 columns on sheet 2.I want to enter data into the 5 cells on sheet 1 and by pressing the SAVE button on sheet 1 transfer the data from the 5 cells on sheet 1 to the corresponding headed columns on sheet 2.
Also when the data is transfered to sheet 2 the 5 cells on sheet 1 are cleared so I can enter new data which would be transfered onto the next line down on sheet 2s coloumns. Im soory this is complicated but Im trying to creat a template that I can use over and over again with all the results on 1 data sheet.
I have attached a simple example.
Thank you

[chewingyu]

Please change the title of your thread to a more meaningful one as it facilitates users to search for the appropriate thread for possible solutions to the problem he/she has.

Thanks.

[Justin Young]

Please see the attached solution. I have gone a little bit overboard with the VBA. I created names for each of the data entry cells. I also added a name at the top left corner of the data log table.

The reason for adding the names is that they make the code more robust. If you choose to move the data entry cells or insert rows/columns around them - the macro continues to work correctly.

Test it out. Let me know what you think.

[Chris Watson]

Thanks Justin this is great, all I have to do now is workout how you did it and use it on a more substancial template, Is it possible to get it to change a week number or date on the template when you press ENTER??

[Justin Young]

To have the macro increment a week number or date I suggest that you first name the cell (I will use aWeekNum as my example name) where this value is to go. Then, somewhere in the macro you add the following line:

Please Login or Register  to view this content.

You could write

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

The square brackets around the name are shorthand.

How does whole macro work? It would take a short essay to explain things properly. You can learn VBA with the macro recorder. Try the following experiment on a new workbook.

1. Name a cell (any cell) aMyCell and type "Heading" into it.
2. Enter test data into the three cells directly below aMyCell.
3. Activate a cell other than aMyCell.
4. Choose Tools | Macro | Record new macro. The record macro dialog box appears.
5. Choose OK to begin recording. A mini toolbar with two icons appears. One is the stop button that you use to finish recording, the other is a tiny grid - the relative reference button.
6. Use the Name Box to jump to aMyCell.
7. Press Ctrl+Down arrow; the active cell will jump to the last occupied cell.
8. Press the relative reference button on the recording toolbar.
9. Press the down arrow once.
10. Press the Stop button on the recording toolbar.

The macro you recorded makes the active cell go to the botton of aMyCell column, regardless of how many entries there are in it (provided there are no blanks). Put some more entries below aMyCell then run the macro. You will see that the active cell goes to the correct place for a new entry. That is the main part of the macro I wrote for you, although this one achieves the task in a different way.

Press Alt+F11 to look at the VBA code you recorded. Examining this code will give you some idea of how the VBA language works. You can then use what you've learnt to decode my macro.







PS Dont forget to mark the thread as solved.

[Chris Watson]

Thamks Justin your a star.