Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
HI
Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate active sheet on sheet selection | Excel Programming | |||
Selection from list on main sheet from suplemental sheet in same w | New Users to Excel | |||
sheet tabs and selection | Excel Programming | |||
search in a sheet and selection? | Excel Discussion (Misc queries) | |||
Sheet selection | Excel Programming |