Macro to check if data has changed and append to another cell

**brigitteMogg** · 01-18-2013, 08:15 AM

Could you provide me with help with creating a macro to do the following please?

1. If user wishes to change the date in Column B the date currently held in column B needs to append to the data in Column C and is formatted with strikethrough.
2. If user wants to change the estimate in Column D the original estimate needs to append to data in Column E and is formatted with striokegthrough
3. If user changes the status of the project to Complete a message box appears to ask for the completed data and this is copied into column I
4. Once user has added the completion date the whole range from a-I is copied into the complete worksheet and added to next available blank line.
5. Once all data has been update I want to copy the range a2:I??end , open a word template and copy it into the bookmark area projects.

**OllieB** · 01-18-2013, 09:07 AM

brigitte,

all steps but the last one.

add the following routines to the worksheet Data

Please Login or Register  to view this content.

create a new module, and insert the following code

Please Login or Register  to view this content.

**brigitteMogg** · 01-18-2013, 10:03 AM

Hi Ollie, this is absolutely brilliant and does everything I asked for. Thank you very much you are a star.

I have just taken out the line 'rngTarget.Parent.Rows(rngTarget.Row).Delete' because I want to keep this data for that reporting period but then want to delete it the following month. Therefore I need another routiune that checks on opening the workbook if the completed project on the data worksheet has been copied into the complete worksheet and deletes the project data from the data worksheet. Do you have any ideas?

**OllieB** · 01-18-2013, 10:28 AM

I think you are better off using a button that, when clicked by the user, removes all project rows with a completion date in the previous month (as determined by the current date) after asking the user whether he/she is really sure. The routine below can be linked to such a command button, or can be called from within the workbook_open event. Up to you :-)

Please Login or Register  to view this content.

**brigitteMogg** · 01-18-2013, 10:35 AM

Again, this works brilliantly, and I agree, adding it to a button is better solution.

With regard to opening a word template and then copying this data across. Although I have had some replies, none of them have worked, whereas yours have worked first time.

Thank you

**OllieB** · 01-18-2013, 10:38 AM

If you want assistance on the export to Word please provide a copy of the Word document and a description of what you would like to happen.

**brigitteMogg** · 01-18-2013, 11:01 AM

Thank you Olli. I have added a copy of the word document that is created when opening a new document from my template. I am unable to upload the template for you to see as ti doesn't accept the file extension. Below is what I want to achieve:

1. Once the user has finished updating their report I want them to press a button, which opens a new word document from a template called "monthly report.dotm" from their 'my template' area
2. I then want it to copy the data from the worksheet "data" from within the sample workbook you have been working with. Goto the bookmark "projects" in the new word doument and paste the data range.

I hope this is clear enough for you

Brigitte

**OllieB** · 01-18-2013, 11:41 AM

Please post the template in a zip file (that is allowed) and I will have a go at writing a solution for you

**brigitteMogg** · 01-18-2013, 11:55 AM

Hi Ollie, that would be absolutely fantastic. I have therefore attached a zip file with the template in.

**brigitteMogg** · 01-18-2013, 01:09 PM

Hi Ollie, I have transferred the excel workbook to another laptop and I am now getting an error message ' cannot find project or library'. Do you have any suggestions on how to resolve this?

**OllieB** · 01-18-2013, 03:18 PM

brigitte, I noticed that there are some special project references which also prevented me from compiling any code. Hence I have only posted the code as text and did not include any workbook to you. Go to Tools/Reference and "deselect" those references with a text MISSING:. That should solve your problem as I do not think you require them. I will try to work on your document export this weekend.

**OllieB** · 01-18-2013, 04:46 PM

brigitte, we never really discussed what you would like to have written into the Projects bookmark section of the Word template so I have just provided the framework below. It should be quite easy to expand on. Perhaps by adding a table to the Projects section and filling that in. Note that the code assumes that the document template will be available in the same directory as the workbook itself.

Try this

Please Login or Register  to view this content.

**OllieB** · 01-18-2013, 04:58 PM

I just noticed you wanted to paste the table on the data worksheet. Revised code below

Please Login or Register  to view this content.

**OllieB** · 01-21-2013, 10:20 AM

Hi Brigitte, is this solution working for you?

**brigitteMogg** · 05-14-2013, 05:06 AM

Hi Ollie

Sorry I haven't replied to your messages, unfortunately my husband died at the end of January and I have only just returned to work. I have a few changes to my spreadsheet that I would like some help with, would you be able to look at these for me?

Brigitte

**brigitteMogg** · 05-16-2013, 11:39 AM

Could I have some assistance in making the cell bold in Column B when date has been changed and has been copied across to column C?

Macro to check if data has changed and append to another cell

LinkBack

Thread Tools

Rate This Thread

Display

Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Re: Macro to check if data has changed and append to another cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions