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

Sheet selection help
 

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
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

Sheet selection help
 

Hi Bob

Thanks for that, but having a similar problem with this one. As follows:-
If the cell is in the worksheet that was saved it works fine
If the cell is in another worksheet the macro fails at that line.

Just to confirm using Excel 2003 in case the VBA changed.

Any thoughts

"Bob Phillips" wrote:

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
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

Sheet selection help
 

Rewriting your code a bit:


Sub Findinworkbook33()
Dim rng As Range, sh As Worksheet, Wks As Worksheet, Wbk As Workbook
Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String
Dim lookfor As String, selectcell As String, i As Long
Application.ScreenUpdating = False
Set Wks = ActiveSheet
sAddr = "A5"
Set Wbk = ActiveWorkbook
lookfor = Wks.Range(sAddr).Value ' value from [ActiveSheet!A5]
Workbooks.Open "workbook 2" ' now the new activeworkbook
i = 5 'counter for your paste
vTarget = lookfor ' what is *lookfor* for?
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
'copies from rng's adjacent found cell to [C5] in 1st wbook
rng(1, 2).Copy Wks.Cells(i, 3)
i = i + 1 ' next found value goes to [C6]
Set rng = .FindNext(rng)
Loop While rng.Address < fAddr
End If
End With
Next sh
'returning to the original sheet & book
Application.Goto Wks.Cells(1, 1), True
End Sub


Regards
Robert McCurdy

"Craig McK" wrote in message ...
Hi Bob

Thanks for that, but having a similar problem with this one. As follows:-
If the cell is in the worksheet that was saved it works fine
If the cell is in another worksheet the macro fails at that line.

Just to confirm using Excel 2003 in case the VBA changed.

Any thoughts

"Bob Phillips" wrote:

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
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

Sheet selection help
 

Okay done some more testing. From my very limited knowledge what seems to be
happening is that the find command does not activate each sheet when it
searches through it.
Any idea how I activate the correct sheet based on the macro below

"Craig McK" wrote:

Hi Bob

Thanks for that, but having a similar problem with this one. As follows:-
If the cell is in the worksheet that was saved it works fine
If the cell is in another worksheet the macro fails at that line.

Just to confirm using Excel 2003 in case the VBA changed.

Any thoughts

"Bob Phillips" wrote:

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
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

Sheet selection help
 

Hi Robert

Thanks for that, I reposted just before yours came up. But you r solution
works just the way I need it

Thanks again
Craig

"Robert McCurdy" wrote:

Rewriting your code a bit:


Sub Findinworkbook33()
Dim rng As Range, sh As Worksheet, Wks As Worksheet, Wbk As Workbook
Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String
Dim lookfor As String, selectcell As String, i As Long
Application.ScreenUpdating = False
Set Wks = ActiveSheet
sAddr = "A5"
Set Wbk = ActiveWorkbook
lookfor = Wks.Range(sAddr).Value ' value from [ActiveSheet!A5]
Workbooks.Open "workbook 2" ' now the new activeworkbook
i = 5 'counter for your paste
vTarget = lookfor ' what is *lookfor* for?
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
'copies from rng's adjacent found cell to [C5] in 1st wbook
rng(1, 2).Copy Wks.Cells(i, 3)
i = i + 1 ' next found value goes to [C6]
Set rng = .FindNext(rng)
Loop While rng.Address < fAddr
End If
End With
Next sh
'returning to the original sheet & book
Application.Goto Wks.Cells(1, 1), True
End Sub


Regards
Robert McCurdy

"Craig McK" wrote in message ...
Hi Bob

Thanks for that, but having a similar problem with this one. As follows:-
If the cell is in the worksheet that was saved it works fine
If the cell is in another worksheet the macro fails at that line.

Just to confirm using Excel 2003 in case the VBA changed.

Any thoughts

"Bob Phillips" wrote:

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
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

Sheet selection help Next Part
 

Robert

Last question for today (Probably!)

I also want to copy a description field that resides in column of the
selected row. I can't work out how to make the rng(1,2).copy...... an
absolute column, can you help

Thanks

"Robert McCurdy" wrote:

Rewriting your code a bit:


Sub Findinworkbook33()
Dim rng As Range, sh As Worksheet, Wks As Worksheet, Wbk As Workbook
Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String
Dim lookfor As String, selectcell As String, i As Long
Application.ScreenUpdating = False
Set Wks = ActiveSheet
sAddr = "A5"
Set Wbk = ActiveWorkbook
lookfor = Wks.Range(sAddr).Value ' value from [ActiveSheet!A5]
Workbooks.Open "workbook 2" ' now the new activeworkbook
i = 5 'counter for your paste
vTarget = lookfor ' what is *lookfor* for?
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
'copies from rng's adjacent found cell to [C5] in 1st wbook
rng(1, 2).Copy Wks.Cells(i, 3)
i = i + 1 ' next found value goes to [C6]
Set rng = .FindNext(rng)
Loop While rng.Address < fAddr
End If
End With
Next sh
'returning to the original sheet & book
Application.Goto Wks.Cells(1, 1), True
End Sub


Regards
Robert McCurdy

"Craig McK" wrote in message ...
Hi Bob

Thanks for that, but having a similar problem with this one. As follows:-
If the cell is in the worksheet that was saved it works fine
If the cell is in another worksheet the macro fails at that line.

Just to confirm using Excel 2003 in case the VBA changed.

Any thoughts

"Bob Phillips" wrote:

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
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