Coding help to transfer cell data to different sheet

[DancingElvis]

All,

Thanks in advance for your support in assisting me develop the attached worksheet.

If you could refer to the attachment in the first instance it will help you with my explanation of what I'm after.

Basically it's a document that enables users on my department to update escalations that they will record and send to a different document that I will use to track escalations recorded by my agents. For the sake of seeking help the 2nd worksheet where the info is sent will be included in this attachment.

In cell ref B5 I would like a formula function to automatically input the time and date.

If it's possible in D5 I need a formula function to input user name of the agent who uses the document to process an escalation......is that possible?? If not then could you suggest a different approach.

I'll be inputting a VLOOKUP in cell ref F5 to look up user name and match against TM.

All the other cells will be updated by agent.

Could you create a macro button called 'Record Contact' and also devise a code so that all the data in the following cells be sent to sheet 2 in the following order:

Sheet1 B5 to Sheet2 B2
Sheet1 D5 to Sheet2 C2
Sheet1 F5 to Sheet2 D3
Sheet1 B10 to Sheet2 E2
Sheet1 D10 to Sheet2 F2
Sheet1 F10 to Sheet2 G2
Sheet1 B15 to Sheet2 H2
Sheet1 F15 to Sheet2 I2
Sheet1 H17 to Sheet2 J2

Could you also ensure that the code is a loop so that any new record goes on a new line and not over write the previous record.

Then once it's recorded clear contents from cells in Sheet1.

Sorry if it's a little long winded - I hope you get the jist of want I'm needing

Thanks DancingElvis

[fredlo2010]

Hello,

See if what I have created helps you.

I modified a little your tabs to add information needed for it to work. Also note that the Sheet "User Names & TM" has to be populated before the workbook can work properly.

I added the following code to copy between the form and the log

Please Login or Register  to view this content.

Also note that I added a User Defined Function to retrieve the username of the person using the workbook.

Please Login or Register  to view this content.

Here is a copy of the workbook.
Attachment 263795

[mehmetcik]

This is all relatively easy.

It would have been a lot easier if you used a userform for the Data Entry.

[HaHoBe]

Hi, fredlo2010,

Application.Username may be changed in any Office-product by the user himself. I would rely on the name used to login to the pc instead:

Please Login or Register  to view this content.

@mehmetcik:
If itīs so easy: where is your code or your sample?

Ciao,
Holger

[fredlo2010]

@ HaHoBe,
Yes... that's what I was looking for I stared at the code and I was thinking all the time "I have seen another way of doing this." You are a genius I totally forgot, This definitively what I was aiming for. ( Learning VBA is a work in progress)

@mehmetcik,
I thought of a Userform to to the job. But I decided to keep it the way it was because its better for the user to troubleshoot something like this than a form. But undoubtedly it would give it a better presentation. and I would not just add a form I would add at least least three: the main one for the data, a second one to update the staff, and of course a splash screen.

@DancingElvis,
As per HaHoBe awesome suggestion I modified the code for the function. Make sure you change it in the file as well.

Please Login or Register  to view this content.

Thanks

[DancingElvis]

Thanks for all your help supporting me in resolving this matter greatly appreciated.

Just two questions fredlo 2010 - if I update the agents user ID's in the User Names & TM tab along with relevant TM info for VLOOKUP purposes that should work...

Also, when I've tested it when I press record it over-writes the 1st record in line 2, doesn't seem to be leaving original record in line 1 and looping new record to line 3

Sorry to be a pain.....

Thank Chris

[fredlo2010]

Hello,

Yes as long as you update the information for the tem the vlookup will work fine. I am using a dynamic range so you will never have to worry about updating it.

Please Login or Register  to view this content.

I updated the record for the issue in sheet where data was not recorded properly. I was counting the last for Column "A" in your sheet and it starts in column "B"

here is the fixed code

Please Login or Register  to view this content.

Here is the updated file.

Attachment 264064

Thanks

[DancingElvis]

Fredlo2010 - again sincerest apologies to be bothering you regarding this, hopefully it'll be the last time regarding this thread: but I'll proably need your help for other problems in the future. First thing, I'm unable to access the attachment you forward with the last thread.

I've brought the spreadsheet from the first thread you provided into work - and updated the macro for the loop issue we had into that, which is working fine. There is one problem however, I'm getting the following error message #NAME? in the two cells that we've input the following function =IFERROR(VLOOKUP(user(),User_ID,2,FALSE),"Update User"). It's probably my fault - we use Excel version 2003 in work, I didn't mention that. I know that 2003 version doesn't have IFERROR function. Is there a workaround???

Also, would it be easy to amend the macro if I need to send the data to a slighty different speadsheet. If not can I password protect the Data worksheet and still have the spreadsheet as a shared document??

Sorry about this - thanks again.

[fredlo2010]

Hello,

See if this helps. I dont have 2003 so I cannot test it.

Thanks

Manager Escalations.xls