Add text from one field to another

[arcsum68]

Hi all, thanks for the help.
Excel 2003

This will either be easy or impossible, not sure, im not expert.

I want to be able to type text into a field and have it add it to text in another field.

Basically I want to create a running log of changes that I can lock, so I can enter text into say A1 and have it copy over to B1. This will also make it much easier to add status without having to edit the log. I have a 1000 row spreadsheet and have to manually go in so I dont overwrite previous data, but if I could just have a column that would move it over adding it I could just edit entire rows.

Hope that makes sense.

[davesexcel]

Not much sense!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[arcsum68]

I will when I get access to it. I will try to explain again.

Basically I want to enter text into a "comments" section. The problem I have now, is when I want to enter 400 lines of comments that are the same I cannot just copy and paste them because it overwrites any current comments.

My thought was have a field that would add the text to the existing text in another column.

so
a2 may have 4 comments, if I enter a comment into a1, it would copy into a2 for a total of 5 comments. I could then delete or overwrite a1 without affecting the data in a2

[foxguy]

Hi arcsum68;

I want to be able to type text into a field and have it add it to text in another field.

Basically I want to create a running log of changes that I can lock, so I can enter text into say A1 and have it copy over to B1. This will also make it much easier to add status without having to edit the log. I have a 1000 row spreadsheet and have to manually go in so I dont overwrite previous data, but if I could just have a column that would move it over adding it I could just edit entire rows.

Are you saying this:
B1 currently contains "ABC", I type "D" into A1. I want B1 to then contain "ABCD" and A1 to contain ""?
or maybe
B1 currently contains "ABC", I type "D" into A1. I want C1 to contain "ABC", B1 to contain "D", and A1 to contain "".
or something else.

[JBeaucaire]

This will require VBA. Here's a worksheet_change macro that will watch column A and anything typed into column A will be added to the value in column B on the same row.

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Right-click on the sheet tab and select VIEW CODE
3. Copy and Paste in your code (given above) in the sheet module that appears
4. Get out of VBA (Press Alt+Q)
5. Save as a macro-enabled workbook

The macro is installed and ready to use. Simply type something anywhere in column A.

[arcsum68]

Hi arcsum68;

Are you saying this:
B1 currently contains "ABC", I type "D" into A1. I want B1 to then contain "ABCD" and A1 to contain ""?
or maybe
B1 currently contains "ABC", I type "D" into A1. I want C1 to contain "ABC", B1 to contain "D", and A1 to contain "".
or something else.

Close, more like this

B1 currently contains
"C
B
A",

I type "D" into A1.

I want B1 to then contain
"D
C
B
A"
and A1 to contain ""? I dont care what A1 contains at this point, if it deleted itself that would be great, or I could just delete everything after updating.

Thanks for the macro, I will check that out and see if it does what I am looking for, I will post back.

[arcsum68]

The macro is the closest thing so far (getting excited!), anyway to make it enter a new line?

looking to add dated comments with the newest at the top something like this, all in the same row

7/1/10 blah blah blah
6/28/10 comment blah blah blah

[foxguy]

Hi arcsum68;
In JBeaucaire's macro change this line to put contents of "A" at the front of "B" instead of the back.

Please Login or Register  to view this content.

anyway to make it enter a new line?
looking to add dated comments with the newest at the top something like this, all in the same row
7/1/10 blah blah blah
6/28/10 comment blah blah blah

You'll have to explain better. How can it "enter a new line" and also "all in the same row". Obviously I don't understand what you want. Try explaining it using cell references like before.

[arcsum68]

You guys are really good, ive gotta say, impressive response. Thank you SO much.

I am equally bad at explaining, so I apologize.

Basically if it would alt+enter the new comment so it would put it on its own line within the row.

If B1 currently read

7/1 blah blah
6/30 blah blah

and I enter in A1

7/2 new blah

B1 would read

7/2 new blah
7/1 blah blah
6/30 blah blah

I think that should be clear on what I want.
*edit* I was losing some formatting, should be more clear now

foxguy, your code enters everything 2x, but i was able to fix it (stab in the dark)

[foxguy]

Basically if it would alt+enter the new comment so it would put it on its own line within the row.

If B1 currently read

7/1 blah blah
6/30 blah blah

and I enter in A1

7/2 new blah

B1 would read

7/2 new blah
7/1 blah blah
6/30 blah blah

What you're saying is that you want to word wrap the column, and you want the macro to determine how much text to put on each line.
That's going to be a lot more difficult, because the macro would have to
1) determine the length of the text to add, accounting for the font used, because each font takes up different amounts of space
2) determine the length of each line already in column B
3) add spaces to each line that is too short to have it's own line
4) ??
I have never tried to do that. It sounds interesting to try, but I don't have the time right now. Maybe in a few days if no one else does it first.

foxguy, your code enters everything 2x, but i was able to fix it (stab in the dark)

Since I only gave you one line of code, I'm guessing that you duplicated the old line instead of changing it.

[Cutter]

change the line in the macro that begins with Range to this

Please Login or Register  to view this content.

[foxguy]

change the line in the macro that begins with Range to this

Please Login or Register  to view this content.

Now why didn't I think of that?

[Cutter]

Let's wait to see if it's what arcsum68 wants.

[arcsum68]

Nearly perfect!

I will have to remove the date formatting, we have to enter the comments from when they were recieved, not the current date. (Just found this out)

Also, its entering a square after the last letter?

[shg]

Change vbCrLf to vbLf

[arcsum68]

AWESOME!!!!

You guys rock! Im just trying to resolve a part of a spreadsheet thats bugging me, you guys could probably save my company a ton of money!

[arcsum68]

Hmmm, for some reason everytime I paste now, it highlights the entire document. I had previously only tried single lines of text.

[arcsum68]

After getting more involved with this its not really working as I had hoped. Entering data works perfectly, but frequently the data entered will need to be on the clipboard and copied to multiple cells.

For example, I might enter "7/12 Emailed PM" for 20 lines, which would all need to be typed out since paste doesn't work.

Would this be possible if it did not auto delete the current line, and then I could clean it up quickly?

[foxguy]

Would this be possible if it did not auto delete the current line, and then I could clean it up quickly?

Assuming that you're still using the macro that Jerry gave you, this would be the corrected macro to not delete column A data. This does not put in a date, I assume that Column A has a date in it.

Please Login or Register  to view this content.

I'm not sure what could be causing the entire sheet to get highlighted. I would have to see the macro that you are using to track it down.

[arcsum68]

foxguy - I thought what you provided was GOLD! However, my co-worker thinks differently.

She says that doing it that way keeps the person entering the comment from editing it if they make a mistake (true, but I did not think it mattered, just be more careful right?).

Would there be anyway of doing exactly what you provided, but AFTER all the data has been entered?

That way they could do whatever they need, then we get the file and move everything over. I know, this is a PITA, but it will save me hours of work and prevent me from getting carpel tunnel

[arcsum68]

I tried to turn it into a runnable macro, but I really don't know what I am doing and get a compile error: expected end sub

[foxguy]

She says that doing it that way keeps the person entering the comment from editing it if they make a mistake (true, but I did not think it mattered, just be more careful right?).

There's no reason they can't edit the cell in column B. The macro won't do a thing, because it ONLY works if column A is changed.

Would there be anyway of doing exactly what you provided, but AFTER all the data has been entered?

Sure:

Please Login or Register  to view this content.

You can then assign this macro to a button, and they would click it at will as many times as they want. The macro will not put the contents of "A" into "B" a second time.

[arcsum68]

They cannot edit "B" because we lock it, this is all part of a vendor contract I am dealing with. We need them to provide updates on payments and issues, but cannot allow them to edit previous comments because we need to have a record of everything.

I tried to use the code you provided and either I am doing it wrong or it does not work. I copied the code, created a new macro, went in to the macro to edit it and pasted the code. For testing purposes I used a generic worksheet with only A and B columns, but I am trying to copy AI to AH

On previous code it was clear where I needed to make changes to the columns, but this one is not very clear to me. Sry.

[foxguy]

Hi arcsum68;
No Problem. I didn't test this, so back up first.

Please Login or Register  to view this content.

[arcsum68]

Imma play with that, but I may already have come up with a solution.

I used your previous code and created two dummy columns, once we verify the data we can then just copy the entire row and paste it into the dummy column which will copy everything to our archive of comments.

Sounds like alot of work, but between that and the other formula to not copy on filtered cells I was able to manipulate 100's of lines of data in seconds that would have normally taken me an hour.

How are you guys doing this, just knowing the code? Or are you using the macro recorder and just know how to manipulate it? Its fantastic!

[foxguy]

How are you guys doing this, just knowing the code? Or are you using the macro recorder and just know how to manipulate it? Its fantastic!

Just doing the same things over & over keeps all the commands, functions, etc. in my mind.

I only use the recorder when I want to do something that I've never done before, but it is never the finished product. The recorded macro just gives me the correct syntax to use, so I don't have to figure it out.

[arcsum68]

Well I certainly appreciate it, wish I understood more about what exactly is happening, hopefully I will get to that point one day.

Heres what I have so far (moved some stuff around today)

Can we excluded the headers, so the entire row 1?

Please Login or Register  to view this content.

[foxguy]

Hi arcsum68;
I can't read the code you posted. I can get in trouble with the moderators for responding to a message without code tags.

1) Go back to the last message.
2) Click "Edit".
3) Click "Go Advanced".
4) Highlight all the lines that are code and click the "#" above the message box.
Another way is to put "[CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]" right after it.

[arcsum68]

Thanks, wasn't sure how to do that!

[foxguy]

Can we excluded the headers, so the entire row 1?

I think you mean that if the user changes AL1 or AM1, don't change "AI1" or "AE1"

I'm confused. In message # 23, you said you wanted a macro to run after all the data had been changed. This macro changes it immediately when the user changes 1 cell.

Also what is this line for?

Please Login or Register  to view this content.

But here is what you asked for. I cleaned it up a little bit.

Please Login or Register  to view this content.

[arcsum68]

[QUOTE=foxguy;2343630]I think you mean that if the user changes AL1 or AM1, don't change "AI1" or "AE1"

I'm confused. In message # 23, you said you wanted a macro to run after all the data had been changed. This macro changes it immediately when the user changes 1 cell.

Also what is this line for?

Please Login or Register  to view this content.

You are correct, but in #25 I said I was able to use the previous code you supplied to make it work how we needed it. We basically added another column and just copy and paste the data into that column, which moves it into the correct format of yet another column. Even more confusing I know, I am 2 weeks into this mess and trying to make life a bit easier. They used to manually go through hundreds of lines>copying text>moving over one cell>alt+entering>arrowing up>pasting. This replaces that by just copying the entire row and pasting into the dummy column which formats it correctly into the final column.

I think its working correctly now, but if it does not I may jump back over to the runnable macro.

That last line is a little gem I picked up from here

http://www.excelforum.com/excel-new-...red-table.html

It is supposed to allow me to copy and paste into a filtered view correctly. Seems to be mostly working with the exception of one of my columns, not sure why.

[foxguy]

[QUOTE=arcsum68;2343724]

That last line is a little gem I picked up from here

http://www.excelforum.com/excel-new-...red-table.html

It is supposed to allow me to copy and paste into a filtered view correctly. Seems to be mostly working with the exception of one of my columns, not sure why.

I think you have it backwards. It's supposed to let you copy FROM a filtered view. As Pike said in message #9 of the link.

Copying the filtered range(s) to other filter range(s) allways encurs and error.

I'm concluding that there is more code in your macro that does more than just update columns AE and AI.

[arcsum68]

Right, not copy from and to, but it allows me to highlight and change a column of filtered data without affecting the hidden data.

Sound better?

And that was EXACTLY my problem with the one column, damn you are helpful!

[foxguy]

Right, not copy from and to, but it allows me to highlight and change a column of filtered data without affecting the hidden data.

Sound better?

And that was EXACTLY my problem with the one column, damn you are helpful!

Sounds right. I am curious though. Are you actually doing some of this manually? If so why didn't you have the macro do it for you?

Don't forget to mark the thread solved and rate the answer if you like it.

[arcsum68]

Well, I am not sure which part your referencing as SO much of it is manual, I am just trying to contain some of the extreme issues right now as the team I joined is 6 months behind.

I think you are referring to the fact that I will manipulate filtered data? If so, its because I get responses from various project managers at different stages. Now I can filter for the specific project and update all the comments in one quick method instead of painstakingly entering comment after comment and also being very careful to not overwrite data thats hidden by the filter.

Just tested and it looks good so far, will have to run it through its paces next week.

Is it normal that everything goes into edit mode (circling ants) after entering some data?

I cannot even image how much time this will save, and I still get asked if we cant make it not enter a blank line if theres nothing already there.

[foxguy]

Hi arcsum68;

Well the next time you do something manually, record it. Then post the recording on the forum and we can tell you how to change it so that the next time you have to do that task, you can just click a button.

[arcsum68]

There is so much done manually, its insane, and so much of it is little bits of data here and there that I cannot even imagine much of it being done automatically. Every Wed we "prepare" the file to review by the vendor and after tonight I am disheartened to say that the code as we have it wont work. I will not allow you to copy and paste more than once, and keeps putting the file into edit mode. I literally spent 2 hours removing formulas and certain columns to reduce the file form 16megs to 6megs, its just crazy how much time is wasted doing this completely manual process and leaves SO much room for error.
I think I am going to go back to the runnable script option (which I am perfectly fine with), as others will be using the file and I just cannot have there be any issues while they are trying to make adjustments.

I will be off the next couple of days, hopefully the code you provided for the runnable script will work and I can move forward with actually making some progress.

[foxguy]

Good Luck;

Let me know if you need any more.

[arcsum68]

Alright, back to the runnable macro, I know this is getting old.

I tried to update with the cells I need to copy (aj to ai) but it does not seem to work, heres what I have, help please!

Please Login or Register  to view this content.

[foxguy]

Hi arcsum68;

I'm sorry, but I can't remember what you're trying to do. I do remember that we provided 2 different macros. 1 that would copy something immediately when a cell was changed, and another one that was triggered by a button to copy all the cells that were changed.

Since there are already 39 posts to this thread, it would just be easier for you to explain what you want, rather than me going back and reading all the posts and trying to follow what happened.

[arcsum68]

Yeah, I know, I am a PITA!

The instant change code was causing some issues when doing manual functions, but worked very well otherwise. So I am back to wanting to do a button that I can use to have it copy everything from AJ to AI, it needs to copy to the top of the cell not the bottom (ie alt+enter)

[foxguy]

Hi arcsum68;

There was a but in the first code. This should fix it.

Please Login or Register  to view this content.

[arcsum68]

Pretty good, not quite as fast as the other method, but its certainly worth it.

Can we make it exclude the first row which has our headers in it?

Row 1

[foxguy]

Hi arcsum68;

This code allows you to have blank rows at the top of the sheet and the macro doesn't bother checking them.

Please Login or Register  to view this content.

If you will NEVER have blank rows at the top and row 1 will ALWAYS have headings then change the line to

Please Login or Register  to view this content.

If you think you might someday insert blank rows above the headings then change it to:

Please Login or Register  to view this content.

[arcsum68]

So I replace the relevant line like this? It seems to be working, so I think thats the ticket.

I used this code

Please Login or Register  to view this content.

How do I rename these?

I was playing around with my test sheet and realized that I can use a macro from another sheet if its open, very nice. So I could create a worksheet with all my macros in it and then not have to save them to the working file?

Please Login or Register  to view this content.

[foxguy]

Hi arcsum68;

Please Login or Register  to view this content.

Yes that is the line. As I said, changing to a "2" makes the assumption that you will NEVER insert any rows above row 1. If you insert a new row above row 1 then the macro will work on your new row 2 (old row 1).

How do I rename these?

rename what?

I was playing around with my test sheet and realized that I can use a macro from another sheet if its open, very nice. So I could create a worksheet with all my macros in it and then not have to save them to the working file?

Sorry, I have no idea what you're talking about.
I have a hunch that you're saying sheet but you mean workbook. If that's right then yes you can run a macro in a different workbook, but you have to be careful. The macro in the other workbook might assume that the Activesheet is in that other workbook. If it uses the ThisWorkbook object, then it might crash when you run it from a different workbook.

Most programmers save macros in their "Personal.xls" workbook and use them in any workbook that is open. That's very common. That's probably the most common use of the Personal.xls workbook. I have over 100 macros in my Personal workbook and I have menu buttons run a lot of them. Like a dozen buttons that format the activecell the way I want, so I don't have to FORMAT>CELL>PATTERN every time, I just click the appropriate menu button and it does it for me.

[arcsum68]

Sorry, I have no idea what you're talking about.
I have a hunch that you're saying sheet but you mean workbook. If that's right then yes you can run a macro in a different workbook, but you have to be careful. The macro in the other workbook might assume that the Activesheet is in that other workbook. If it uses the ThisWorkbook object, then it might crash when you run it from a different workbook.

Most programmers save macros in their "Personal.xls" workbook and use them in any workbook that is open. That's very common. That's probably the most common use of the Personal.xls workbook. I have over 100 macros in my Personal workbook and I have menu buttons run a lot of them. Like a dozen buttons that format the activecell the way I want, so I don't have to FORMAT>CELL>PATTERN every time, I just click the appropriate menu button and it does it for me.

Thats exactly what I was referring to, since they are all the same type of workbook I will probably just have everyone agree that my way (ie your way) is better since it really is.

How do I rename the macro once its created and working properly? right now its called update_b

[foxguy]

Hi arcsum68;

How do I rename the macro once its created and working properly? right now its called update_b

Just type over the old name to whatever you want. When you change the name it will automatically show up in the list of macros with the new name.
One issue is whether you have assigned the macro to any buttons or menu selections. If so, you will have to go back ans reassign them.

[arcsum68]

Also, ive saved something to personal.xls, but it says its locked and to use the unlock command. Im not sure where to find that.

[arcsum68]

I cannot find a personal.xls anywhere on my computer using windows search.

Also, I am not sure where I should be overwriting the name of the macro, but nothing allows me to edit the macro name.

[foxguy]

Hi

Also, ive saved something to personal.xls, but it says its locked and to use the unlock command. Im not sure where to find that.

In menus WINDOW>UNHIDE... you should see personal.xls there.
If it's not there, then record a macro. In menus TOOLS>MACRO>RECORD NEW MACRO... That will open a window asking you to name the macro and where to store it. Select "Personal Macro Workbook" and OK. Then just move the cursor to some other cell and stop recording TOOLS>MACRO>STOP RECORDING.
Now you will have a Person.xls that you can use to store macros in.

Also, I am not sure where I should be overwriting the name of the macro, but nothing allows me to edit the macro name.

Click Alt+F8. This will open the macro window for you to select a macro. Then click "Edit". That will open a window with the cursor sitting below the line "Sub Update_B()". Just change it to "Sub myMacro()" or whatever you want.

[arcsum68]

Thanks for that.

Ive been playing with the macro some more and it seems inconsistent. If I enter/manipulate data it works perfectly, however, if I copy and paste an entire column from another workbook it does not seem to want to copy it over.

Looks like it does not like the code I have to allow me to edit filtered data, I could live without it, but it sure is nice. Removing seems to make it work properly.

Please Login or Register  to view this content.

[foxguy]

arcsum68;

You lost me. The code I wrote would have no effect on your ability to copy. My code goes in after the fact and finds cells in the AJ column that have not been put into the AI column yet and puts them in. There is no interaction between my code and some manual action you are doing.
I will admit that my code doesn't distinguish between hidden or visible cells. It does assume that you want ALL non empty AJs put into the corresponding AI, regardless of their hidden or filtered status.

I would be interested in seeing what you're doing to see if I overlooked something.

[arcsum68]

Well, I am not sure what happened. I tried to duplicate the error today so I could give you and exact accounting, but it worked.
Yesterday, if I ran the macro, the VB window would open and be pointing toward that code that allows me to edit filtered data. Perhaps it had something to do with the personal.xls not being correct, but it seems to be working OK now that I have that sorted out.

I do not really know what to say, but I will keep plugging away trying to make it break again.

Thanks so much, you have no idea how much time/wrist life will be saved by this simple little task being automated. I will post back after some more usage, it should get a pretty good workout on Monday.

[foxguy]

You're welcome.

Be sure and mark the thread solved, and if you liked the answer, please rate it by clicking the blue scales in the upper right corner of the message.

[arcsum68]

So, as I promised it got a good workout yesterday. As I have stated a hundred times, there is still a lot of manual things but this working is huge, and it is. Thank you very much!!!! I will continue to look for ways to make everything quicker and more automated.

I think its been to long for me to mark it as solved myself, so if someone could handle that? Or let me know how to get it taken care of.