Value pasting data from a refreshable form to another sheet

Hello,

I have created an excel form that people fill in, click on a button which sends a mail of their form to their manager and then the form is refreshed to go blank.

The data that is input into this form needs to go to another sheet in the same workbook which acts as a database that keeps compiling data from the form. My problem when I use the value paste macro is that when the form is refreshed and someone new comes in to fill the form, old data from the database disappears while the new entry stays even after refreshing.

Any suggestions ?

Thank you so much for the reply. I have attached the sheet here. What I want the excel to do is pick data from certain entries in sheet 1 and put it in sheet 2. Now, I have put a simple IF function for that but what I need is for the data to be value pasted ion sheet 2. While this too might be easy the real problem is as below -

This sheet will go up on sharepoint. So someone selecs their name, fills in details, clicks on the buttons to send mails to people and then clicks refresh and the sheet goes blank. My problem is, I am able to put in a macro that value pastes, but after refresh, when a new entry is put and refresh is pressed again, the old values disappear.

I cant tell you how grateful I would be if you could help me out on this.

Sounds like you're 95% there, if your form is outputting the data you want to the columns you want... you just need to tell it to input this data to the next empty row, and not the same row every time.

This snippet of code is from my digital cut log file, and it makes my userform do exactly what it sounds like you need yours to do:

Please Login or Register  to view this content.

You'll note that my code puts the current date in column "B", not a value entered in a combobox… aside from that, it basically takes the combobox fields and fills the values into the next empty row on your sheet.

Let me know if this works for your application, if not I can try to help you tweak it until it does.

Oh, and I didn't see your sample attachment anywhere, you may need to try uploading it again...

Ok. I am gonna try this again. Attached is the sheet. Il also repeat what I am trying to do -

- This excel will be up on sharepoint

- The DailyLog WIP sheet will be used by employees as a form to fill in things on a daily basis

- Data Compilation sheet will keep a log of all data and enter in the correct row and column based on employee name - date of entry and type of entry

- The problem is once refresh button is pressed on the DailyLogWip by an employee, the data disappears from the Data compilation sheet as well

- I did a macro for value pastign data which worked but when a new entry was made, the old entry disappeared from the compilation sheet.

Hope this was clear and you find the attachment this time.

Originally Posted by 6StringJazzer

No file. Sometimes this happens if the file exceeds size limitations; the error message when that occurs is not very noticable. If your Excel file is over 1MB, then you can put it in a zip file, which can be up to 9.77MB.

has the file gone through ? anybody with any ideas ?

Originally Posted by Ankith200689

has the file gone through ? anybody with any ideas ?

I was able to download the file... where in the file is your code stored that writes the data to the sheet? If I can see how you're doing that, I may be able to help.

Please Login or Register  to view this content.

You should never ever ever do this. There are two problems here.

First, this macro will clear the contents of a selection no matter what worksheet or what file is currently active. Your macro appears to be designed to work on a specific sheet, so you should explicitly use that sheet here by inserting the line

Please Login or Register  to view this content.

Second, you have established a shortcut for this macro as CTRL+r, which is the default Excel shortcut for "fill right". It is not a good idea to re-use existing shortcuts.

I had your file open when I was working on another file for work, and when I selected a range and hit CTRL+r to fill right, your macro executed and deleted my data.

Currently I am using the IF Function on the second sheet. I need a macro that does not erase this data that goes to sheet 2 even after the 1st sheet is refreshed and a new person enters the data.

Originally Posted by 6StringJazzer

Please Login or Register  to view this content.

You should never ever ever do this. There are two problems here.

First, this macro will clear the contents of a selection no matter what worksheet or what file is currently active. Your macro appears to be designed to work on a specific sheet, so you should explicitly use that sheet here by inserting the line

Please Login or Register  to view this content.

Second, you have established a shortcut for this macro as CTRL+r, which is the default Excel shortcut for "fill right". It is not a good idea to re-use existing shortcuts.

I had your file open when I was working on another file for work, and when I selected a range and hit CTRL+r to fill right, your macro executed and deleted my data.

I am really sorry for the trouble. New to the macro world. This would be one of my lessons. Sorry if this caused you any trouble.