Solved Identify data changes, then copies and inserts rows

November 14, 2018 at 15:02:53
Specs: Windows 7
I need a VBA macro that identifies a change of data on sheet 1 in a certain range (a2:b300). Then it needs to copy the entire row/s that has the changed data and inserts it/them into sheets 2 through 8.

See More: Identify data changes, then copies and inserts rows

Reply ↓  Report •

✔ Best Answer
November 15, 2018 at 08:24:44
I'm confused. These 2 statements don't seem to work together very well:

1 - I really do mean insert them. I would like them to be inserted in the same place that they're copied from Sheet 1.

2 - So, I need the rows from Sheet 1 that change to be copied and inserted into the other sheets without overriding the data in Sheets 2 through 8 except for Columns A and B.

I think that you and I may have 2 different definitions of "insert".

When I (and Excel and VBA) use the term "insert" we mean that a destination for the copied row will be determined and the existing data in that destination will be moved down to make room for the copied row. e.g. If Sheet 1 Row 4 is changed, then based on statement #1, that Row should be inserted at Row 4 of Sheets 2:8. That means the current Row 4 of Sheets 2:8 will be moved down to Row 5, Row 5 will be moved down to Row 6, etc. That's what "insert" means.

If that's the case, then statement #2 is superfluous since an inserted row never overwrites existing data. The existing data is moved out of the way first.

Could it be that you actually mean that the data in the copied Row should replace the existing data in the same Row in Sheets 2:8?

If "replace" is really what you mean and if only columns A & B are being changed on Sheet 1, why wouldn't you want the code to simply copy/paste the data in Sheet1 Ax:Bx to the other sheets? Columns C through the last column would never be overwritten.

Then you said:

If a deletion or addition occurs in Sheet 1, I'd like that same action to occur in the other sheets.

OK, so let's say the user adds (inserts) a Row below Row 6 on Sheet 1. It sounds like you want the code to add (insert) a Row below Row 6 on Sheets 2:8. That sounds doable.

But then you said:

Sheets 2 through 8 hold shift time details that differ each day as well as the Starting Date column.

If the shift time data is not in the new row on Sheet1, isn't the user going to have to go into each sheet (2:8) and manually add that data? Since you on;y want the data from Sheet 1 A:B copied to the other sheet, how is all the other data going to get into Sheets 2:8?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
November 14, 2018 at 15:19:36
Well, you've told us what you need.

If you want some help with that, feel free to ask.


message edited by DerbyDad03


Reply ↓  Report •

#2
November 14, 2018 at 15:33:37
I'm terribly sorry folks. You're right, I didn't even realise I didn't ask.

Can I please have some help with this Macro? It would be greatly appreciated.

Thanks in advance.


Reply ↓  Report •

#3
November 14, 2018 at 15:36:30
I forgot to mention that I only need the changed rows in sheet 1 (including added/deleted rows) to be inserted into sheets 2 through 8, not the whole range.

I can't have the rows with unchanged data disturbed in sheets 2 through 8.

Is that possible?

message edited by Reiper79


Reply ↓  Report •

Related Solutions

#4
November 14, 2018 at 18:26:53
We need more information before we could offer any suggestions.

re: "inserts it/them into sheets 2 through 8"

What do you mean by "inserts them"? Do you mean "append them" i.e. place them in the next empty row or do you really mean "insert them"? If you mean "insert them" then how will the code know where to insert them? What is the criteria that determines the location to insert them?

You mention sheets "2 through 8". What determines which row(s) get inserted into which sheet?

re: "including added/deleted rows"

Are you saying that after a user deletes a row you want the row copied to the another sheet?

You do realize that once something is deleted it is no longer available to be copied, don't you?

That requirement is going to be tough to accomplish. The code would need to copy/paste the row in anticipation of it being deleted. Even if the row was just "copied" in anticipation of being deleted, as soon as the row was deleted the clipboard will be cleared and there will be nothing left to paste. The row would need to be copied/pasted before is was deleted.

If you could supply the exact process that your users follow and exactly what you expect the macro to do, we'll see if we can help.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
November 14, 2018 at 19:40:20
Thanks for the reply. Please see below in BOLD and ITALIC for my answers.

re: "inserts it/them into sheets 2 through 8"

What do you mean by "inserts them"? Do you mean "append them" i.e. place them in the next empty row or do you really mean "insert them"? If you mean "insert them" then how will the code know where to insert them? What is the criteria that determines the location to insert them? I really do mean insert them. I would like them to be inserted in the same place that they're copied from Sheet 1.

You mention sheets "2 through 8". What determines which row(s) get inserted into which sheet? Sheets 2 through 8 are identical to Sheet 1 except for a couple of columns that will get values manually entered on each page. The columns that will change in Sheet 1 are A and B. So, I need the rows from Sheet 1 that change to be copied and inserted into the other sheets without overriding the data in Sheets 2 through 8 except for Columns A and B. Hopefully that makes sense.

re: "including added/deleted rows"

Are you saying that after a user deletes a row you want the row copied to the another sheet? Nope. If a deletion or addition occurs in Sheet 1, I'd like that same action to occur in the other sheets.

You do realize that once something is deleted it is no longer available to be copied, don't you? Yep.

That requirement is going to be tough to accomplish. The code would need to copy/paste the row in anticipation of it being deleted. Even if the row was just "copied" in anticipation of being deleted, as soon as the row was deleted the clipboard will be cleared and there will be nothing left to paste. The row would need to be copied/pasted before is was deleted.

If you could supply the exact process that your users follow and exactly what you expect the macro to do, we'll see if we can help.

It is a Driver roster. Sheet 1 through 8 are identical when blank except for the Starting Date column. The Starting Date column on each page has a formula that calculates a specific date automatically. Sheet 1 holds the master list of drivers in columns A and B. The other columns are blank primarily to match the other sheets except the Starting Date column. Sheets 2 through 8 hold shift time details that differ each day as well as the Starting Date column. When the Operations Manager updates the roster, he would go into the sheet relating to each day and update the shifts. If he needs to delete, add or change a driver's details, he would do this via the master list and have the macro insert those changes into the other sheets. This would save him from copying and pasting all those details manually. Hopefully that all makes sense.

message edited by Reiper79


Reply ↓  Report •

#6
November 15, 2018 at 08:24:44
✔ Best Answer
I'm confused. These 2 statements don't seem to work together very well:

1 - I really do mean insert them. I would like them to be inserted in the same place that they're copied from Sheet 1.

2 - So, I need the rows from Sheet 1 that change to be copied and inserted into the other sheets without overriding the data in Sheets 2 through 8 except for Columns A and B.

I think that you and I may have 2 different definitions of "insert".

When I (and Excel and VBA) use the term "insert" we mean that a destination for the copied row will be determined and the existing data in that destination will be moved down to make room for the copied row. e.g. If Sheet 1 Row 4 is changed, then based on statement #1, that Row should be inserted at Row 4 of Sheets 2:8. That means the current Row 4 of Sheets 2:8 will be moved down to Row 5, Row 5 will be moved down to Row 6, etc. That's what "insert" means.

If that's the case, then statement #2 is superfluous since an inserted row never overwrites existing data. The existing data is moved out of the way first.

Could it be that you actually mean that the data in the copied Row should replace the existing data in the same Row in Sheets 2:8?

If "replace" is really what you mean and if only columns A & B are being changed on Sheet 1, why wouldn't you want the code to simply copy/paste the data in Sheet1 Ax:Bx to the other sheets? Columns C through the last column would never be overwritten.

Then you said:

If a deletion or addition occurs in Sheet 1, I'd like that same action to occur in the other sheets.

OK, so let's say the user adds (inserts) a Row below Row 6 on Sheet 1. It sounds like you want the code to add (insert) a Row below Row 6 on Sheets 2:8. That sounds doable.

But then you said:

Sheets 2 through 8 hold shift time details that differ each day as well as the Starting Date column.

If the shift time data is not in the new row on Sheet1, isn't the user going to have to go into each sheet (2:8) and manually add that data? Since you on;y want the data from Sheet 1 A:B copied to the other sheet, how is all the other data going to get into Sheets 2:8?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
November 16, 2018 at 19:53:42
Thanks for your input to date. Unfortunately, explaining what I am trying to achieve in writing is becoming quite difficult. I would like to send you a copy of the spreadsheet so you can see what I am talking about, but it contains personal information of our employees.


I think I will come at my problem a different way and try something different.


Thanks again and sorry for confusing you so much.


Reply ↓  Report •

Ask Question