Code to update asset numbers automatically

[irrefutable14]

I have a list of asset numbers that are on sheet 1, column A. Then on Sheet 2, column A I have to enter the asset number (not in the same order listed in sheet 1, column a) along with the member its assigned to. However sometimes the asset numbers change... What I would like to have excel do is; whenever an asset number is updated in sheet 1 column A; I want it to automatically find that old number on sheet 2 column A and replace/update it with the new asset number... is that possible???

 

[Rick Rothstein]

Re: is this possible?!?!?!?!?

hMM... Maybe you could take a look at my sheet. Could be because I have other formulas in place that its not working. [/CODE]

DOWNLOAD LINK
http://www.dropbox.com/s/xcsrsaoj2gsigvp/TabletCheckin_out.xlsm?dl=0

The problem may be, partly, that my code is not robust enough for what you are doing on your worksheets. I'll give you revised code to try in a moment, but first...

1) I cannot tell which of your named sheets is equivalent to Sheet1 and Sheet2 that you originally told us your sheets were named BUT it looks to me like you may have put my code in the wrong sheet module OR you it is in the correct sheet module but the sheet references are wrong. By the way, for future reference, please do not simplify your data or its layout when posting a question in a forum... doing so just means you will have to try and equate what we post for you to what you actually have (and you are now seeing the problem with your having to do that). If you tell us what you have, we will design our solution for exactly that.

2) Some of your formulas apparently reference data in other workbooks which we, of course, cannot see, so your worksheet comes up mostly blank with lots of error cells.

3)Formulas in other columns have no bearing on what you asked us to do (substitute text constants on one sheet for text constants on another sheet).

Okay, here is my (untested) revised code (still using Sheet1 and Sheet2 for the worksheet references)...

[table="width: 500"]
[tr]
	[td]Dim CurrentCellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Target.Column = 1 Then Sheets("Sheet2").Columns("A").Replace CurrentCellValue, Target.Value, , xlWhole, , , False
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count = 1 Then
    If Target.Column = 1 Then If Target.Count = 1 Then CurrentCellValue = Target.Value
  End If
End Sub[/td]
[/tr]
[/table]

 

[irrefutable14]

Re: is this possible?!?!?!?!?

My apologies. I'm totally a novice to excel and the form. Here's the entire sheet(s) name "Assign ASUS HERE" & "ASUS CHECKIN".
What I'm trying to get accomplished is on "Assign ASUS HERE" I want to scan numbers into columns A2-B2,then have it automatically
go down a row; A3-B3.. So on. And if a value in "Assign ASUS HERE" Column A is updated. On sheet "ASUS CHECKIN" I'd like it to auto update.

On "assign Tablets HERE" & "IPAD CHECKIN-IN" I'd like Column A in "assign tablets here" to do the same with with ONLY the auto update if there are changes made to column A in "ipad checkin-in"
I thought I just needed a code not, a code that was tailored. Here is the entire workbook.

https://www.dropbox.com/s/2qv3d4n2kt981ng/Tablet CheckIn_Out123.xlsm?dl=0

 

[irrefutable14]

Re: is this possible?!?!?!?!?

Here's the entire sheet(s) name "Assign ASUS HERE" & "ASUS CHECKIN".
What I'm trying to get accomplished is on "Assign ASUS HERE" I want to scan numbers into columns A2-B2,then have it automatically
go down a row; A3-B3.. So on. And if a value in "Assign ASUS HERE" Column A is updated. On sheet "ASUS CHECKIN" I'd like it to auto update.

On "assign Tablets HERE" & "IPAD CHECKIN-IN" I'd like Column A in "assign tablets here" to do the same with with ONLY the auto update if there are changes made to column A in "ipad checkin-in"
I thought I just needed a code not, a code that was tailored. Here is the entire workbook.

https://www.dropbox.com/s/2qv3d4n2kt...t123.xlsm?dl=0