Home

when cell value changes, copy the information to a new worksheet but also retain each change

%3CLINGO-SUB%20id%3D%22lingo-sub-182681%22%20slang%3D%22en-US%22%3Ewhen%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182681%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3EHow%20would%20I%20copy%20a%20cell%20(%20or%20row%20actually)%20to%20a%20new%20worksheet%20when%20I%20make%20a%20change%20to%20a%20particular%20cell%20in%20the%20first%20worksheet%3F%20AND%20whenever%20I%20make%20a%20change%20to%20that%20particular%20cell%20it%20would%20not%20only%20make%20a%20copy%20of%20it%20but%20would%20continue%20to%20make%20copies%20onto%20a%20different%20worksheet%20without%20overwriting%20itself%3F%3C%2FP%3E%3CP%3Eexample%3A%3C%2FP%3E%3CP%3Esheet%201%3C%2FP%3E%3CP%3EB2%20has%20a%20value.%20I%20change%20the%20value.%20It%20copies%20the%20value%20to%20sheet%202.%20I%20then%20change%20the%20value%20on%20sheet%201%20again.%20It%20again%20copies%20the%20value%20onto%20sheet%202%20but%20it%20doesn't%20overwrite%20it%2C%20it%20just%20populates%20the%20next%20row%20number%20over.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-182681%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EChanging%20cell%20value%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-331782%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331782%22%20slang%3D%22en-US%22%3E%3CP%3ELogaraj%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20problem%20as%20what%20you%20helped%20hopr37%20with.%26nbsp%3B%20I%20have%20a%20worksheet%20that%20keeps%20track%20of%20labor.%26nbsp%3B%20When%20we%20take%20a%20new%20job%2C%20labor%20gets%20added%20to%20it%2C%20and%20when%20we%20complete%20portions%20of%20the%20labor%2C%20or%20ship%20a%20completed%20job%2C%20the%20labor%20comes%20off.%26nbsp%3B%20What%20I'd%20like%20to%20do%20is%20keep%20track%20of%20labor%20as%20it%20happens.%26nbsp%3B%20We%20run%20into%20a%20problem%20when%20we%20ship%20lets%20say%20a%20job%20that%20has%2010%20hours%20of%20labor%20on%20it%2C%20and%20then%20take%20on%20a%20job%20that%20has%2010%20hours%20of%20labor%20on%20it%20in%20the%20same%20day.%26nbsp%3B%20The%20number%20changes%2C%20but%20changes%20back%20to%20the%20original%20amount%2C%20so%20we%20don't%20know%20how%20efficient%20jobs%20are%20moving%20through%20production.%26nbsp%3B%20I'd%20like%20to%20record%20any%20increase%20changes%20to%20that%20number%20in%20one%20cell%2C%20and%20any%20decreases%20to%20that%20number%20in%20a%20different%20cell.%26nbsp%3B%20For%20a%20twist%2C%20I%20want%20those%20changes%20to%20change%20throughout%20the%20week%2C%20as%20long%20as%20TODAY()'s%20date%20is%20before%20a%20date%20I%20have%20listed%20in%20another%20cell.%26nbsp%3B%20Does%20this%20make%20sense%3F%26nbsp%3B%20I'd%20rather%20not%20post%20the%20worksheet%20to%20the%20forum%2C%20but%20will%20send%20it%20to%20you%20directly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-184134%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-184134%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20works%20great.%20Thank%20you%20so%20much%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-184030%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-184030%22%20slang%3D%22en-US%22%3E%3CP%3Ehopr37%20hotmail%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20This%20is%20answer%20for%20your%20first%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20If%20you%20have%20any%20headers%20or%20any%20value%20in%20the%20range%20%22C1%2CC2%2CC3%22%2C%20there%20is%20no%20problem.%20You%20can%20continue%20with%20the%20following.%20If%20not%2C%20put%26nbsp%3Bspace%20in%20blank%20cells%20in%20C1%2CC2%2CC3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Not%20Intersect(Target%2C%20Range(%22B%3AB%22))%20Is%20Nothing%20Then%3CBR%20%2F%3EActiveCell.Offset(-1%2C%200).Activate%3CBR%20%2F%3Ea%20%3D%20Sheets(%22Sheet2%22).Cells(Rows.Count%2C%20%22C%22).End(xlUp).Row%20%2B%201%3CBR%20%2F%3ESheets(%22Sheet2%22).Range(%22C%22%20%26amp%3B%20a).Value%20%3D%20ActiveCell.Value%3CBR%20%2F%3EActiveCell.Offset(1%2C%200).Select%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Just%20clarify%20the%20following.%3C%2FP%3E%3CP%3Ea.%20So%20if%20you%20have%20value%20%222%22%20in%20%22Sheet2%22%2C%20this%20will%20result%20to%20bring%20above%20cell's%20value.%3C%2FP%3E%3CP%3EEg.%20%22Sheet2%22%2C%20%22C6%22%20value%20is%202%2C%20it%20will%20bring%20%22C5%22%20value.%3C%2FP%3E%3CP%3E%3CSPAN%3E%22Sheet2%22%2C%20%22C10%22%20value%20is%202%2C%20it%20will%20bring%20%22%3C%2FSPAN%3E%3CSPAN%3EC9%22%20value.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERight%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20this%20is%20not%20you%20want.%20Just%20explain%20me%20step%20by%20step.%20Put%20your%20questions%20one%20by%20one%20not%20in%20same%20paragraph.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EIf%20you%20gave%20example%2C%20i%20will%20solve%20your%20question%20easily.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183695%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183695%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20works%20great!%3C%2FP%3E%3CP%3EOne%20question.%20Can%20I%20have%20it%20start%20at%20a%20particular%20cell%3F%3C%2FP%3E%3CP%3EIt%20wants%20to%20start%20off%20by%20copying%20the%20headers%20I'm%20using%20and%20I%20need%20it%20to%20start%20at%20C4.%3C%2FP%3E%3CP%3Eactually.%20After%20trying%20it%2C%20it%20seems%20to%20post%20the%20number%20above%20the%20cell%20I%20change.%3C%2FP%3E%3CP%3ESo%20if%20C4%3D%20a%20value%20of%202%20and%20I%20change%20that%20value%2C%20it%20copies%20value%20in%20C3%20to%20the%20second%20worksheet.%3C%2FP%3E%3CP%3Eso%20every%20cell%20I%20change%20on%20the%20first%20sheet%20copies%20the%20value%20from%20sheet%201%20from%20the%20cell%20ABOVE%20the%20cell%20value%20I%20change.%3C%2FP%3E%3CP%3Edoes%20that%20make%20sense%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20got%20it...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183673%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183673%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20after%20entering%20the%20value%20press%20enter%2C%20don't%20try%20downarrow%20%2F%20tab%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Not%20Intersect(Target%2C%20Range(%22B%3AB%22))%20Is%20Nothing%20Then%3CBR%20%2F%3Ea%20%3D%20Sheets(%22Sheet2%22).Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%20%2B%201%3CBR%20%2F%3EActiveCell.Offset(-1%2C%200).Activate%3CBR%20%2F%3ESheets(%22Sheet2%22).Range(%22A%22%20%26amp%3B%20a).Value%20%3D%20ActiveCell.Value%3CBR%20%2F%3EActiveCell.Offset(1%2C%200).Select%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183626%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183626%22%20slang%3D%22en-US%22%3E%3CP%3EHad%20to%20change%20a%20value%20and%20it's%20working.%20Thank%20you%20so%20much.%3C%2FP%3E%3CP%3ENow%20%3A)%3C%2FP%3E%3CP%3EI%20didn't%20want%20to%20look%20at%20too%20much%20of%20that%20code%20until%20I%20understand%20it%20however%2C%20is%20it%20now%20possible%20to%20have%20the%20same%20effect%20but%20instead%20of%20just%20changing%20one%20cell%20value%20it%20could%20do%20the%20same%20thing%20no%20matter%20what%20cell%20I%20change%20in%20column%20B%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eassuming%3A%26nbsp%3B%3CSPAN%3EIf%20Target.Address%20%3D%20%22%24B%242%22%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Emeans%20column%20B%20cell2%2C%20could%20this%20be%20changed%20so%20that%20any%20cell%20in%20column%20B%20could%20do%20the%20same%20thing%20and%20not%20just%20column%20B%20cell%202%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183625%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183625%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20reply%20but%20it%20doesn't%20seem%20to%20do%20anything.%20I'll%20keep%20seeing%20what%20I'm%20doing%20wrong%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183579%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183579%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3Bhopr37%20hotmail%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssume%20you%20have%202%20sheets%20like%20(Sheet1%2C%20Sheet2).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Right%20click%20the%20Sheet1%20tab%20-%26gt%3B%20Click%20View%20Code%3C%2FP%3E%3CP%3E2.%20Paste%20the%20following.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Target.Address%20%3D%20%22%24B%242%22%20Then%3CBR%20%2F%3Ea%20%3D%20Sheets(%22Sheet2%22).Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%20%2B%201%3CBR%20%2F%3ESheets(%22Sheet2%22).Range(%22A%22%20%26amp%3B%20a).Value%20%3D%20Sheets(%22Sheet1%22).Range(%22B2%22).Value%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182799%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182799%22%20slang%3D%22en-US%22%3E%3CP%3Eanyone%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751032%22%20slang%3D%22en-US%22%3ERe%3A%20when%20cell%20value%20changes%2C%20copy%20the%20information%20to%20a%20new%20worksheet%20but%20also%20retain%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53299%22%20target%3D%22_blank%22%3E%40Logaraj%20Sekar%3C%2FA%3Ethe%20VBA%20code%20works%20perfect%20IF%20the%20data%20is%20inputted%20by%20hand%2C%20but%20if%20the%20data%20is%20changed%20by%20reference%20to%20another%20cell%20or%20workbook%20i.e.%20A1%3D%20sheet6%20C1%20and%20C1%20changes%20the%20A1%20value%20then%20it%20does%20-%20unfortunately!!!%20-%20not%20work.%20Rescue%20please!%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
hopr37 hotmail
Contributor

Hello.

How would I copy a cell ( or row actually) to a new worksheet when I make a change to a particular cell in the first worksheet? AND whenever I make a change to that particular cell it would not only make a copy of it but would continue to make copies onto a different worksheet without overwriting itself?

example:

sheet 1

B2 has a value. I change the value. It copies the value to sheet 2. I then change the value on sheet 1 again. It again copies the value onto sheet 2 but it doesn't overwrite it, it just populates the next row number over.

10 Replies

Hi hopr37 hotmail,

 

Assume you have 2 sheets like (Sheet1, Sheet2).

 

1. Right click the Sheet1 tab -> Click View Code

2. Paste the following.

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & a).Value = Sheets("Sheet1").Range("B2").Value
End If
End Sub

 

 

 

Thank you for the reply but it doesn't seem to do anything. I'll keep seeing what I'm doing wrong

Had to change a value and it's working. Thank you so much.

Now :)

I didn't want to look at too much of that code until I understand it however, is it now possible to have the same effect but instead of just changing one cell value it could do the same thing no matter what cell I change in column B?

 

assuming: If Target.Address = "$B$2" 

means column B cell2, could this be changed so that any cell in column B could do the same thing and not just column B cell 2?

Try this.

 

But after entering the value press enter, don't try downarrow / tab .

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(-1, 0).Activate
Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
End Sub

 

That works great!

One question. Can I have it start at a particular cell?

It wants to start off by copying the headers I'm using and I need it to start at C4.

actually. After trying it, it seems to post the number above the cell I change.

So if C4= a value of 2 and I change that value, it copies value in C3 to the second worksheet.

so every cell I change on the first sheet copies the value from sheet 1 from the cell ABOVE the cell value I change.

does that make sense?

 

I think I got it...

hopr37 hotmail

 

1. This is answer for your first question.

 

Note: If you have any headers or any value in the range "C1,C2,C3", there is no problem. You can continue with the following. If not, put space in blank cells in C1,C2,C3.

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
ActiveCell.Offset(-1, 0).Activate
a = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row + 1
Sheets("Sheet2").Range("C" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
End Sub

 

 

2. Just clarify the following.

a. So if you have value "2" in "Sheet2", this will result to bring above cell's value.

Eg. "Sheet2", "C6" value is 2, it will bring "C5" value.

"Sheet2", "C10" value is 2, it will bring "C9" value.

 

Right?

 

If this is not you want. Just explain me step by step. Put your questions one by one not in same paragraph. If you gave example, i will solve your question easily.

That works great. Thank you so much for your help!

Logaraj,

 

I have a similar problem as what you helped hopr37 with.  I have a worksheet that keeps track of labor.  When we take a new job, labor gets added to it, and when we complete portions of the labor, or ship a completed job, the labor comes off.  What I'd like to do is keep track of labor as it happens.  We run into a problem when we ship lets say a job that has 10 hours of labor on it, and then take on a job that has 10 hours of labor on it in the same day.  The number changes, but changes back to the original amount, so we don't know how efficient jobs are moving through production.  I'd like to record any increase changes to that number in one cell, and any decreases to that number in a different cell.  For a twist, I want those changes to change throughout the week, as long as TODAY()'s date is before a date I have listed in another cell.  Does this make sense?  I'd rather not post the worksheet to the forum, but will send it to you directly.

 

Thank you

@Logaraj Sekarthe VBA code works perfect IF the data is inputted by hand, but if the data is changed by reference to another cell or workbook i.e. A1= sheet6 C1 and C1 changes the A1 value then it does - unfortunately!!! - not work. Rescue please!?

Related Conversations