SOLVED

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

Brass 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.

80 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?

best response confirmed by cuong (Microsoft)
Solution

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!?

@Logaraj Sekar 

 

Dear Logaraj_Sekar,

 

I have noticed that you have helped someone with a similar problem to mine. I have used your code successfully. However, I would also like to add "and" function in my statement.You can see what I have done below (it is not currently working):

 

Private Sub Worksheet_Change(ByVal Target As Range)

If "B1" = "EURO" And Target.Address = "$B$2" Then

a = Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Row + 1

Sheets("Sheet4").Range("A" & a).Value = Sheets("Sheet2").Range("B2").Value

Else

If "B1" = "USD" And Target.Address = "$B$2" Then

a = Sheets("Sheet4").Cells(Rows.Count, "B").End(xlUp).Row + 1

Sheets("Sheet4").Range("B" & a).Value = Sheets("Sheet2").Range("B2").Value

End If

End Sub

 

I would like to specify that if B1 contains the word "EURO" and the target address for the changing values is B2 from sheet 2, then I would like the values from B2 to be placed in column A in sheet 4, else if B1 contains the word "USD" and the target address for the changing values is B2 from sheet 2, I would like the values from B2 to be placed in column B in sheet 4.

 

Would you be able to help me with this issue?

 

Thank you

@natalib12 

 

Try this.

 

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

@Logaraj Sekar Hello , I see you helped many people , so I hope you can help me out as well...

I have a worksheet with products I got in offer listed on it, what I am trying to do now is when I type number of pcs in amount in sheet2 to automatically copy selected columns of that row to sheet1 on the list of offer and move selection on next row so I would need only to type amounts in sheet2 and then move to sheet1 where it would all be listed. 

What I need is if value of cell H3 - H98 in sheet1 is higher than 0 to do following:

- Copy range B3-B98 from sheet2 to range B15-B53 on sheet1

- Copy range H3-H98 on sheet2 to E15-E53 on sheet1 

- Copy range I3-I98 on sheet2 to F15-F53 on sheet1 

(So just those mentioned rows where column H is higher than 0 should be copied and after coping to move to cell below where it will copy second product with amount higher than 0 again without moving to sheet1 until its all done)

Is this possible to be done?

 

I appologize if I was not clear as English is not my first language , so if something is not clear please let me know... And thanks for your help in advance!

@Despot91 

 

Hi, Sorry now only i saw ur message.

 

If you already found solution, pls ignore this reply. If not, pls send sample worksheet.

@Logaraj Sekar 

Hello friend , thanks for your reply. No I have not found solution yet unfortunately :(:(
I deliver you file in attachment... There is some description writen in Serbian (it is basically what I wrote in message to you just shee1 and shee2 are renamed to otpremnica and ponuda) , I could translate it for you if needed when I get to PC , just let me know.

 

Thanks in advance for your help!

I still did not find a solution for problem mentioned above , if anyone can help it will be appreciated...

@Despot91,

 

I am working on it.

 

First, I've met trouble with your language.

 

Second, The worksheet contains 3 sheets : Ponuda, Otpremnica, Sheet1. But in question raised you have not mentioned sheets clearly. you have mentioned sheet1, sheet2. We don't understand which is sheet1 and which is sheet2.

 

So please give it clear or with example.

@Logaraj Sekar 

I appologize for sending it that way. I couldnt open file from my laptop because of older verison of excel so I sent on my language. And thank you for making a file. That is what I need, except in sheet otpremnica it should all go one below another and it also should copy price. 

 

I deliver you file with description in English.

This is what idea is:

When you insert quantity bigger than 0 , to automatically transfer to sheet "Otpremnica" name of article , quantity and price eur customer… (Price is not copied in file you sent)
More in detail:
When some row from column H > 0 ,so first positive number in that column (doesnt matter if that is H10 , H15 or...) to copy following:
(Example: h10 , h3, h15 we insert quantity exactly in that order):

1. Insert quantity to H10
- H10 sheet Ponuda to copy to E15 sheet Otpremnica ,
- B10 sheet Ponuda to copy to B15 sheet Otpremnica,
- I10 sheet Ponuda to copy to F15 sheet Otpremnica

2. Insert quantity to H3
- H3 sheet Ponuda to copy to E16 sheet Otpremnica ,
- B3 sheet Ponuda to copy to B16 sheet Otpremnica,
- I3 sheet Ponuda to copy to F16 sheet Otpremnica

3. Insert quantity to H15
- H15 sheet Ponuda to copy to E17 sheet Otpremnica ,
- B15 sheet Ponuda to copy to B17 sheet Otpremnica,
- I15 sheet Ponuda to copy to F17 sheet Otpremnica

^This to be applied for row 3 - row 1500 on sheet Ponuda

 

Please let me know if there still is something confusing. And once again thanks for help! 

1 best response

Accepted Solutions
best response confirmed by cuong (Microsoft)
Solution

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

 

View solution in original post