Copy value to different sheet on cell change.

[mrme58]

So I have this sheet. In J:J I have selectable lists to show a status (Open, Closed etc). When this status is changed I have the date of the change automagically entered into another cell K:K. I also want it to copy the new value of the changed cell and the date in the K:K Cell into a seperate sheet, a log book of changes as it were. Please help, VBA is not something i am very familiar with.

 

[Tom Urtis]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

Any clue as to what the destination sheet name is, and where on it (what cell addresses) you want those changed dates and values?????

 

[mrme58]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

Hi, Yes The value from Sheet 1 A:A should go to Sheet2 A:A, value from Sheet 1 J:J to Sheet2 B:B and Sheet 1 K:K to Sheet2 C:C. This would create a log of every status change with the ID, New Status and Date/Time of the change.

 

[Tom Urtis]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

That's a bit different than how your subject line reads and what you first wrote. I see you are new to this forum, I'm not trying to be difficult or to bedevil you with questions, but you can see how clarifications can be needed to provide an answer you can actually use, depending on what you're working with. So to be clear, you have 3 cells on Sheet1 that will be copied to Sheet2. That raises 4 more questions:

(1)
How do the values in Sheet1 get there... by manual entry? formula? copied from elsewhere? imported?

(2)
At what point shall the copy take place from Sheet1 to Sheet2... when all 3 cells are occupied? when any one or two of those cells are occupied?

(3)
Supposing that a copy and paste action takes place today, what happens if any values in those cells become changed again on Sheet1... do they get copied and pasted again to Sheet2? or just ignored because only one copy and paste per record on Sheet1 be executed.

(4)
In case it is that all 3 cells must be occupied on Sheet1 before a copy and paste to Sheet2 is executed, and after that a copy and paste to Sheet2 does get executed, what shall become of the pasted values on Sheet2 is a theretofore copied cell on Sheet1 has its contents deleted... delete the 3-cell recordset on Sheet2? delete the value in that associated cell on Sheet2? ignore the deletion and just grab a beer?

 

[mrme58]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

Thank you for taking the time to look at this. My background is web based but i am making a concerted effort to learn VBA and Excel in general. This is in a drive to show we do not need SmartSheets as most functionality can be done in excel with a little learning and help. Hope the below clears up my intentions.

Sheet 1[TABLE="width: 500"]
<tbody>[TR]
[TD]Id[/TD]
[TD]Defect No[/TD]
[TD]Date Reported[/TD]
[TD]Reported By[/TD]
[TD]Nature of Defect[/TD]
[TD]Status[/TD]
[TD]Last Update to status[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12345[/TD]
[TD]1/1/2019[/TD]
[TD]Jo Blogs[/TD]
[TD]Some Defect[/TD]
[TD]Dropdown[/TD]
[TD]1/1/2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123456[/TD]
[TD]2/1/2019[/TD]
[TD]Dave[/TD]
[TD]Some other defect[/TD]
[TD]Dropdown[/TD]
[TD]1/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Id[/TD]
[TD]Status[/TD]
[TD]Status updated[/TD]
[/TR]
[TR]
[TD]From Id[/TD]
[TD]From Status[/TD]
[TD]From Last Update to status.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(1) The values in sheet 1.
Id is autogenerated when a line is populated using =IF(NOT(ISBLANK(B2)),1,"")
Status is from a auto validation dropdown with Open, Closed, Awaiting parts etc
Last Update to status by VBA. Updates on every change to Status dropdown.
(2) The copy should take place after the Last update field has been updated or on a change to the status.
(3) It should copy to the next free row in sheet 2 therefore creating a log of status changes and the time these have happened. So if i filter by Id i will see a history of status changes.
(4) It is not intended that any records be deleted. A new sheet may be started at a regular intervals.

Thank you

 

[Tom Urtis]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

OK, so that means there are 3 cells of interest on Sheet1 that get changed, and each of those 3 changing cells are changed from different sources: formula for ID in column A, manual for Status in column F, and VBA for Last Update in column G. To me, that means you have a Change even already in your worksheet module and that is what would be best to utilize for this case. So, because only one event type per object module is allowed (Change event in this case), your existing Change event code needs to be edited to determine if all 3 cells are occupied, and if so, to make the transfer to Sheet2. Therefore, please post your existing Change event code and we can see where the insertion of that new code should go. It will also confirm for me the column locations of the 3 cells you care about being changed.

 

[mrme58]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

See Below.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("J:J"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[Tom Urtis]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

Well, I count 7 fields in your example from post #5 above, but you also say you have data up to column K which is at least 11 columns because you wrote:
"The value from Sheet 1 A:A should go to Sheet2 A:A, value from Sheet 1 J:J to Sheet2 B:B and Sheet 1 K:K to Sheet2 C:C"

Assuming that what you wrote as quoted here is what you really have, then see the code I added to your existing procedure that theoretically should do what you seem to say you want.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("J:J"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next

Dim lngTargetRow as Long, lngNextRow as Long
lngTargetRow = Target.Row
With Sheets("Sheet2")
lngNextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
.cells(lngNextRow, 1).Value = cells(lngTargetRow, 1).Value
.cells(lngNextRow, 2).Value = cells(lngTargetRow, 10).Value
.cells(lngNextRow, 3).Value = cells(lngTargetRow, 11).Value
End With

Application.EnableEvents = True
End If
End Sub

 

[mrme58]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
	Dim WorkRng As Range
	Dim Rng As Range
	Dim xOffsetColumn As Integer
	Set WorkRng = Intersect(Application.ActiveSheet.Range("J:J"), Target)
	xOffsetColumn = 1
	If Not WorkRng Is Nothing Then
		Application.EnableEvents = False
		For Each Rng In WorkRng
			If Not VBA.IsEmpty(Rng.Value) Then
				Rng.Offset(0, xOffsetColumn).Value = Now
				Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
			Else
				Rng.Offset(0, xOffsetColumn).ClearContents
			End If
		Next
		
		
		Dim lngTargetRow As Long, lngNextRow As Long
		lngTargetRow = Target.Row
		With Sheets("Defect Tracker")
			lngNextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
			.Cells(lngNextRow, 1).Value = Cells(lngTargetRow, 1).Value
			.Cells(lngNextRow, 2).Value = Cells(lngTargetRow, 10).Value
			.Cells(lngNextRow, 3).Value = Cells(lngTargetRow, 11).Value
		End With
		
		
		Application.EnableEvents = True
	End If
End Sub

This is what I ended up with and it works perfectly. Thank you for taking the time to help me out with this Tom.

To make it easier to understand for anyone going back can I change the numbers in this line .cells(lngNextRow, 3).Value = cells(lngTargetRow, 11).Value To named ranges?

 

[Tom Urtis]

Re: Banging my head against a VBA Brick wall. Copy value to different sheet on cell change.

To answer your question, it would be a useless effort to make those locations as named ranges. The variable for the next available row means you would have a named range for every record. Why do that when you already have the records' index numbers in column A and dates of change in column whatever. This scenario is not a candidate for named ranges of all these rows.