Copy entered data for a specific date using a command button

Hi,

I'm trying to transfer entered data for each day over to another worksheet 'Data' into their retrospective columns rather than having to enter the data then copy & pasting it over each time. I was thinking a command button could be one way to do it, when the button is clicked it transfers the data over to that particular date. This is being captured so I can see improvements from the previous day or days.

I hope this makes sense, I've attached the workbook so it's easier to understand.

Thanks in advance

Which range do you want to copy, and where to?

I can't add attachments due to server issues so I'll try and explain....

I require C19:C29 to be copied to column D in the 'Data' worksheet under NCR & add the date it is copied in column C in the 'Data' worksheet, i.e if it was today's data then it would add 27/03/2013 and copy it over to that column under the correct name i.e Name 1, Name 2 etc

I need this for all the columns that are in the 'Data' worksheet i.e F19:F29 in the 'League' worksheet would copy to column E with the date on the 'Data' worksheet, I19:I29 in the 'League' worksheet would copy to column E in the 'Data' worksheet and so forth....

I hope this makes sense otherwise I will wait until I can upload files again & highlight it within the workbook.

Thanks for your help on this!

Sure, no problem. But before writing the macro, have you considered doing it with using the functiongs index and match?

I am happy with whatever is easiest to be honest, if you feel this is the way to go then feel free to do it using formulas?

Either way as long as it works!

Thanks Mayda

See the uploaded workbook.
Note that there are some errors, with the following reasons:
1-Some player names you want to lookup don't exist on the lookup range.
2-Some of the column headers you want to lookup either have different names in the lookup range, or don't exist there.
I have marked those that don't exist with red, and those that are named differently with yellow.

The only drawback is you have to enter the dates manually, which I think won't be a problem with so few cells that are adjoiningly located .

Yet, if you desperately need a macro to enter the date, just ask

Thanks for your help so far but this is capturing the same data for every date as I change it, I need it to only change it specifically for the date it is entered. I've edited the worksheet & reuploaded. This is why I was thinking a command button would possibly work better, II enter the data & the date on the form & it copies it all over the corresponding date to the master data sheet. Let me know your thoughts & hopefully it will make more sense....

Thanks again!

I couldn't understand what you mean by:

Originally Posted by SChapman

but this is capturing the same data for every date as I change it, I need it to only change it specifically for the date it is entered.

Could you elaborate a little bit?

When I change the data in let's say...C19 in 'League!' it updates in both D2 & D13 in 'Data!'. I need it to add it under the column NCR in 'Data!' but add today's date (in column C) and the name (in column B).

Does that make sense?

Well, then even if we would use a button to run a macro, it would be hard to tell excel which cells to update and which cells not to update.

So let me understand it better:
When you change some data in the 'League' sheet on a given date, do you change a single cell, or a row at a time?

I'm asking this, because I'm trying to understand when data related to a player is changed, do all the columns for that player get changed, or just some of them?

Single cells relating to the employee (Name 1, Name 2, Name 3...) & column (NCR, RCR, NRR...) then capture on the date that it is entered. My logic to make this work would be when the date is changed in B1 in the 'League' sheet, it defines which date to add to column C in the 'Data' sheet. Not sure if this would work or not?

Thanks again for your patience with this Mayda

No Problem
But still, your answer is unclear; you said:

Originally Posted by SChapman

Single cells relating to the employee (Name 1, Name 2, Name 3...) & column (NCR, RCR, NRR...)

So on a given date, for a given employee, when you change one of the column values (like NCR, or RCR), do you always change the rest of the column values for that employee?

Kind of, I would need it changing only on the date I update it. So if NCR was changed under Name 1, that would add in the 'Data' sheet....Name 1, today's date & the new data i.e 31.00% and the same if RCR, NRR etc were changed.

So sometimes you change only one of the column values, and sometimes you might change all of them (for a given employee)?

And I would be glad if you could try to be more clear on next posts

Yes that is correct. Sorry about the explanations but it's not as easy as it seems to try & explain exactly what is required first time round. Hopefully we've got there now

One last thing:
When copying the values from 'league' sheet to 'data' sheet for a given employee, do you want to replace the existing data about this employee on the 'data' sheet or do you want to add a new line (showing the current date) and paste all the new information there?
(In this case, the table on the 'data' sheet will keep on growing everyday)

I wish to add new lines as it will be a master record.

Thanks

Ok, another question:
Say, today, you updated the NCR field for person1, but didn't update the other fields for him. (you didnt update anything about other persons)
Now do you want excel to copy only the NCR field to the newly added row, and let the other columns at this row stay empty, until new records are pasted; or do you want excel to copy all of the fields to the newly added row?

I will more than likely be updating all fields at the same time each day but this does depend on other reports being updated. So, yes if you can let the other columns stay empty & copy over if they are updated too.

By copy over, do you mean copy to the cell where we left empty?

I know you may feel like "enough with these questions", but I have to ask them because these information are crucial to the code, and you are not specifying them yourself without me asking.

I have no problem with you asking questions, I would rather that than waste your time. Yes I want any of the columns I update in the 'League' spreadsheet on a particular date to copy over to the corresponding column in the 'Data' sheet with the date it was updated

You mean copy over the row where we left the corresponding columns empty or to a whole new row?

Again SChapman, please be more specific in your answers :/

Sorry, I've not long got in from work. Long day!

Ok, so I update the row starting Name 1 in the League sheet, the corresponding cells in that row need to update in the same way in the data sheet but with a date stamp for that day. If Name 2 is updated the same will happen but copy over in the row below Name 1 in the Data sheet.

I really hope that makes sense, if not then let me know.

Hi Mayda,

Have you had any joy with this?

I appreciate your help.