Linking rows between sheets so that a macro can update when new data is entered

[thecaitfish]

Hi guys, me again!

I'm not sure if this goes beyond the functionality of Excel (I think what I probably need is a database, but Excel is all I have to be working with!), but here it goes:

I've got a macro that runs on a number of sheets (Non Conformance Index, IANC Index, Complaint Index) that takes data from certain cells in a row and copies it to the last available row in a master sheet in the same workbook (CAPA Index). This works great for us, but we've got a problem when all the data isn't entered in the Non Conformance Index, for example, and needs to be entered and updated later. Is there any way of 'linking' the original data in the Non Conformance Index and the row with the copied data in the CAPA Index so that a macro can run to update that row rather than putting it in as a new one?

To be clear, this is what I've got running so far:

Please Login or Register  to view this content.

But say for example I run it a few more times and add some more rows to the master sheet, but I now go back and enter more data into the cells involved in that original operation, and need that to update the row that it copied to rather than create a whole new one. Is that possible?

[RichTea88]

Hey Caitfish,

Can you post an example worksheet?

[RichTea88]

Does the Row from "Non-Conformance" that's been copied to CAPA Index contain any unique data? perhaps you have a column with an ID number or something.

We could program it so when you enter new data we can use that unique data to perform a search in the CAPA index and then update that row when it finds it.

[AlphaFrog]

In your macro, copy the data as you do now, and also record on the master sheet the row it was copied to in the same row as the original data. Then later if you have a second macro that updates the additions\changes to the original data, it has the row number where to paste the changes to.

[thecaitfish]

Hey, sure thing. Attached is a mocked up version of the workbook, with some data already entered.

Each of the rows on the CAPA Index sheet have been populated using macros run on the other subsidiary sheets, but the data for Non Conformance No 1001 is incomplete (there is no "action required" or "date completed" entered). This will happen from time to time when we're inputting some data before all the complaint reports have been completed, so some cells will be left blank with the intention that we'll fill them in when the report is done. So say I want to fill in the "action required" bit of Non Conformance no 1001, I'd ideally like something that will transfer that data to the appropriate cell on the CAPA Index (here it'd be G2, but that'll change with each row. Is there any way of getting Excel to do that?

Each row does have some unique data - the Non conformance/IANC/complaint number will be entirely unique to the row (1001, 1002, 1003, 2002 in the eg workbook).

AlphaFrog, I'm not sure how I'd record the row on the master sheet? I started using VBA just before Christmas, so I'm still a super-novice trying to get my head around things!

[AlphaFrog]

Maybe something like this (not tested)

Please Login or Register  to view this content.

[thecaitfish]

That's certainly a more elegant way of doing all the copy-pasting, but it still seems to create a new row when I run it on a row that has already been transferred to the master sheet. So, I just ran it on Non Conformance No 1001 with a few blank cells, put it some other data on the CAPA Index, filled in the rest of the blank cells on 1001, then tried running your script again but it just created a new row, when I need it to update the row in the CAPA Index that refers to Non Conformance no. 1001. (Sorry, I don't know if that makes any sense, I'm a bit of a spanner at explaining things)

[AlphaFrog]

That's certainly a more elegant way of doing all the copy-pasting, but it still seems to create a new row when I run it on a row that has already been transferred to the master sheet. So, I just ran it on Non Conformance No 1001 with a few blank cells, put it some other data on the CAPA Index, filled in the rest of the blank cells on 1001, then tried running your script again but it just created a new row, when I need it to update the row in the CAPA Index that refers to Non Conformance no. 1001. (Sorry, I don't know if that makes any sense, I'm a bit of a spanner at explaining things)

It makes perfect sense.

The code only adds the destination row number to the master sheet for new data rows. It doesn't have the code to update existing rows. I was leaving that part up to you. I don't know what cells you are updating\changing.

Here's how it might work. You probably need to modify this further.

Please Login or Register  to view this content.