Excel 2007 - Look up a cell in a tab [Closed]

Report
-
 Ann -
Within tab 1 I have a cell with an entry of Road 1. In the next cell I wish to go to the Road 1 tab and copy the contents of a cell.

The entry Road 1 may change to e.g Road 2 and I then wish it to go to the Road 2 tab.

I need to use this cell to identify which tab to use as it will change. There relevant tab will always exist.

Can anyone help with this please?

Thanks,
Ann

3 replies

Implement the following code instead of the other code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ws = Range("A1").Value
Range("B1").Value = Sheets(ws).Range("B1").Value
End Sub


Upon entering a sheetname in cell "A1" of sheet "sheet1", the value of cell "B1" of the entered sheetnames sheet will be put in cell "B1" of sheet "sheet1".
If the entered sheetname isn't an actual sheet, nothing happens.

Does this suit your needs?

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Hi Ann,

I've created a workbook with three sheets: Sheet1, Road 1, Road 2.
Then I entered the sheetname e.g. Road 1 in cell A1 of sheet1.
The cell next to that (cell B1) contains the data which will be copied.

Now go to Sheet1, right click on the tab and select the last option (view code) and insert the following code:

Private Sub Worksheet_Deactivate()
ws = Sheets("Sheet1").Range("A1").Value
Sheets("sheet1").Range("B1").Copy
Sheets(ws).Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
Sheets("sheet1").Range("B1").ClearContents
Application.CutCopyMode = False
End Sub


Now enter Road 1 in cell A1 of sheet1 and a value in cell B1.
Then goto sheet Road 1 and the entered value will be there.

Note that the value will be removed.
Also note that you can create a dropdown list for selecting your sheetname by using date validation.

Let me know if everything is clear and/or if you want some adjustments.

Best regards,
Trowa
Sorry I haven't explained very well.

I've created a workbook with three sheets: Sheet1, Road 1, Road 2.
Then I entered the sheetname e.g. Road 1 in cell A1 of sheet1.

I then want to go to the sheetname Road 1 and copy a cell from B! to Sheet1 cell B1.

The content of Sheet1 A1 will change hence I need a way to identify which tab to go to to get the value.

I do not want the values to be removed from Road 1 as they may change and will then be automatically updated within Sheet1.

Thanks,
Ann
Many thanks for this, it is really appreciated.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!