HELP Needed

neills · May 23rd, 2002, 05:43 PM

can anybody help
i want to be able to find data from another workbook and display data from it. I have a master workbook with a lot of sheets, and i want to find a given sheet and copy information from it to a new book.
For example i want to enter the value 676 in cell C1 in workbook NEW, i then want it to find the sheet in workbook MASTER that has that value(676) in B2 and then display the information from cell D7 in that sheet, in cell E10 in workbook NEW.

I hope this is understandable and achievable

with thanks Neill

brettvba · May 23rd, 2002, 07:39 PM

Hi

if you don't want the end result hard coded post back.Sub PutCelval()
Application.ScreenUpdating = False
Dim celval, newval, thiswkbname, masterbook
thiswkbname = ActiveWorkbook.Name
masterbook = "Book3"
celval = ActiveCell.Value
Windows(masterbook).Activate
Cells.Find(What:=(celval), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Activate
newval = ActiveCell.Offset(5, 2)
Windows(thiswkbname).Activate
Range("e10").Value = newval

End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 05:43 PM	#1
neills Board Regular Join Date: May 2002 Location: wirral uk Posts: 104	can anybody help i want to be able to find data from another workbook and display data from it. I have a master workbook with a lot of sheets, and i want to find a given sheet and copy information from it to a new book. For example i want to enter the value 676 in cell C1 in workbook NEW, i then want it to find the sheet in workbook MASTER that has that value(676) in B2 and then display the information from cell D7 in that sheet, in cell E10 in workbook NEW. I hope this is understandable and achievable with thanks Neill

May 23rd, 2002, 07:39 PM	#2
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	Hi if you don't want the end result hard coded post back.Sub PutCelval() Application.ScreenUpdating = False Dim celval, newval, thiswkbname, masterbook thiswkbname = ActiveWorkbook.Name masterbook = "Book3" celval = ActiveCell.Value Windows(masterbook).Activate Cells.Find(What:=(celval), After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _ , SearchFormat:=False).Activate newval = ActiveCell.Offset(5, 2) Windows(thiswkbname).Activate Range("e10").Value = newval End Sub