Macro fill cell based on value from another sheet - columns change

[tcy0330]

Hi all,

I could use a bit of help here.

I have a file with a master list of data, titled "Values." In this sheet, I have specific identifying columns - Building #, Floor # and Unit. (Columns B, C and D, respectively). This sheet contains information for all units on all floors in 5 different buildings.

I then have separate sheets for each separate building, with all the same column headings.

What I want to do is have the column for "sale price" sheets for the individual building autofill when a price is entered in to the "Values" sheet [column F is sale price].

I know I could use a VLOOKUP, but I would like a macro for this, unless someone can give me a good solution. My problem is that I (and others) will be sorting the data in the sheets based on multiple factors. For example, I may want to sort by the units in a building (i.e. price for each "A" unit) or by floors (all units on 5th floor) or, on the master sheet, an advance search by first building, then floor, etc... I'm also concerned about someone using the sheet and deleting the vlookup formula in the columns!

Is there a way to do this? Does my description make sense?

Thanks for all the help!

 

[Legacy 221039]

Hi Tom

I'm slightly confused by the code - the line ".Cells(ii, 6) = shvalues.Cells(j, 6)" writes the values to column 6 of the relevant worksheet, but what part of the code writes values to column 6 of the Values worksheet? (I see the findkey variable, but I can't see what writes the findkey variable to column 6). Any chance you could install Mr Excel HTML maker (see link below) and then show how the macro is altering the data (i.e. a before and after view of the worksheets)? Sorry I'm not exactly answering the question, but I want to fully understand the code first. Kind regards Mark

link:
http://www.mrexcel.com/forum/showthread.php?515787-Forum-Posting-Guidelines&p=2545970#post2545970

 

[Legacy 221039]

Hi Tom

I've had a second look at this. I hope I'm answering the question you are asking...the code below (in this case pasted into sheet1 in the visual basic editor in a brand new workbook) shows a message box whenever the user enters a value anywhere in the "area" F5:H500.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F5:H500")) Is Nothing Then UpdatePrice
End Sub

Sub UpdatePrice()
x = 1
MsgBox x
End Sub