[jfeka[_2_]]

Is there any way to get a macro to execute on a workbook
which is open, but not active?

I have a macro in a certain workbook (call it "A")which
repeats automatically but if I I open another workbook,
say "B", then the macro produces an error because it
doesn't find the data it needs in the active worksheet if
I'm working in "B".

I've added the following code to the start of my macro

Workbooks("hilowwork book.xls").Activate
Worksheets("UpdateInfo").Activate

and the macro runs fine but it causes the sheet to become
active and it interrupts the work I may be doing in
workbook "B".

Is there some way I can feed the macro the information of
the workbook name and sheet name that it is to operate on
so that it does this in the background and still allows
me to continue working in workbook "B" without
interruption?

[John Green[_2_]]

You can refer to cells in non-active workbooks by fully qualifying the references. Something like:

x = Workbooks("A.xls").Worksheets("Sheet1").Range("A1" ).Value

If you need to make lots of references to the same object, use the following:

With Workbooks("A.xls").Worksheets("Sheet1")
x = .Range("A1").Value
.Range("A2").Value = x

End With

--

John Green - Excel MVP
Sydney
Australia


"jfeka" wrote in message ...
Is there any way to get a macro to execute on a workbook
which is open, but not active?

I have a macro in a certain workbook (call it "A")which
repeats automatically but if I I open another workbook,
say "B", then the macro produces an error because it
doesn't find the data it needs in the active worksheet if
I'm working in "B".

I've added the following code to the start of my macro

Workbooks("hilowwork book.xls").Activate
Worksheets("UpdateInfo").Activate

and the macro runs fine but it causes the sheet to become
active and it interrupts the work I may be doing in
workbook "B".

Is there some way I can feed the macro the information of
the workbook name and sheet name that it is to operate on
so that it does this in the background and still allows
me to continue working in workbook "B" without
interruption?

[jfeka[_2_]]

John,

I've tried what you suggest and it produces an error
right off ... here's what I'm doing (below) What is
wrong?

Sub upDateHis1()

booknm = Workbooks("hilowwork book.xls").Worksheets
("UpdateInfo")
lwrLimit = Range("lwr").Row + 1
uprLimit = Range("eend").Row - 1
lgOpnHi = Range("OpnHi").Column
lgStokNow = Range("StokNow").Column
lgStokhi = lgStokNow + 1


For rwIndex = lwrLimit To uprLimit

'This sets High Value of Option
With Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi)
If .Value _
< Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi - 1).Value _
Then .Value = Worksheets("UpdateInfo").Cells
(rwIndex, lgOpnHi - 1).Value
If .Value * 0.75 _
Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi - 1).Value _
Then Worksheets("UpdateInfo").Cells
(rwIndex, 4).Value _
= "SELL"
End With
'This sets high value of Stock
If Worksheets("UpdateInfo").Cells(rwIndex,
lgStokNow).Value _
Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi).Value _
Then
Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi).Value _
= Worksheets("UpdateInfo").Cells(rwIndex,
lgStokNow).Value

Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi + 1).Value _
= Now
End If

Next rwIndex

rePeater

End Sub



-----Original Message-----
You can refer to cells in non-active workbooks by fully
qualifying the references. Something like:

x = Workbooks("A.xls").Worksheets("Sheet1").Range
("A1").Value

If you need to make lots of references to the same
object, use the following:

With Workbooks("A.xls").Worksheets("Sheet1")
x = .Range("A1").Value
.Range("A2").Value = x

End With

--

John Green - Excel MVP
Sydney
Australia


"jfeka" wrote in message
...
Is there any way to get a macro to execute on a
workbook
which is open, but not active?

I have a macro in a certain workbook (call it "A")which
repeats automatically but if I I open another workbook,
say "B", then the macro produces an error because it
doesn't find the data it needs in the active worksheet
if
I'm working in "B".

I've added the following code to the start of my macro

Workbooks("hilowwork book.xls").Activate
Worksheets("UpdateInfo").Activate

and the macro runs fine but it causes the sheet to
become
active and it interrupts the work I may be doing in
workbook "B".

Is there some way I can feed the macro the information
of
the workbook name and sheet name that it is to operate
on
so that it does this in the background and still allows
me to continue working in workbook "B" without
interruption?



.

[John Green[_2_]]

Your code will not work if the workbook is not active as you are not including a reference to the workbook.

Also, if you are trying to create an object variable referring to the worksheet, use Set

Set booknm = Workbooks("hilowwork book.xls").Worksheets("UpdateInfo")

For rwIndex = lwrLimit To uprLimit

'This sets High Value of Option
With booknm.Cells(rwIndex, lgOpnHi)
If .Value _
< booknm.Cells(rwIndex, lgOpnHi - 1).Value _
Then .Value = booknm.Cells(rwIndex, lgOpnHi - 1).Value


--

John Green - Excel MVP
Sydney
Australia


"jfeka" wrote in message ...
John,

I've tried what you suggest and it produces an error
right off ... here's what I'm doing (below) What is
wrong?

Sub upDateHis1()

booknm = Workbooks("hilowwork book.xls").Worksheets
("UpdateInfo")
lwrLimit = Range("lwr").Row + 1
uprLimit = Range("eend").Row - 1
lgOpnHi = Range("OpnHi").Column
lgStokNow = Range("StokNow").Column
lgStokhi = lgStokNow + 1


For rwIndex = lwrLimit To uprLimit

'This sets High Value of Option
With Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi)
If .Value _
< Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi - 1).Value _
Then .Value = Worksheets("UpdateInfo").Cells
(rwIndex, lgOpnHi - 1).Value
If .Value * 0.75 _
Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi - 1).Value _
Then Worksheets("UpdateInfo").Cells
(rwIndex, 4).Value _
= "SELL"
End With
'This sets high value of Stock
If Worksheets("UpdateInfo").Cells(rwIndex,
lgStokNow).Value _
Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi).Value _
Then
Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi).Value _
= Worksheets("UpdateInfo").Cells(rwIndex,
lgStokNow).Value

Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi + 1).Value _
= Now
End If

Next rwIndex

rePeater

End Sub



-----Original Message-----
You can refer to cells in non-active workbooks by fully
qualifying the references. Something like:

x = Workbooks("A.xls").Worksheets("Sheet1").Range
("A1").Value

If you need to make lots of references to the same
object, use the following:

With Workbooks("A.xls").Worksheets("Sheet1")
x = .Range("A1").Value
.Range("A2").Value = x

End With

--

John Green - Excel MVP
Sydney
Australia


"jfeka" wrote in message
...
Is there any way to get a macro to execute on a
workbook
which is open, but not active?

I have a macro in a certain workbook (call it "A")which
repeats automatically but if I I open another workbook,
say "B", then the macro produces an error because it
doesn't find the data it needs in the active worksheet
if
I'm working in "B".

I've added the following code to the start of my macro

Workbooks("hilowwork book.xls").Activate
Worksheets("UpdateInfo").Activate

and the macro runs fine but it causes the sheet to
become
active and it interrupts the work I may be doing in
workbook "B".

Is there some way I can feed the macro the information
of
the workbook name and sheet name that it is to operate
on
so that it does this in the background and still allows
me to continue working in workbook "B" without
interruption?



.

[jack[_5_]]

Thanks John,

Your comment about creating an object referring the the
worksheet worked like a charm.

I had tried that before, but without using "SET" and it
wouldn't work and I had forgotten about the difference.

Anyhow, this has also solved the problem I had with the
stopping of a running macro, because now I can let it go
on in the background and don't need to pause it.

The other answer will be tucked awa for future
reference...it still may come in useful.

Jack

-----Original Message-----
Your code will not work if the workbook is not active as
you are not including a reference to the workbook.

Also, if you are trying to create an object variable
referring to the worksheet, use Set

Set booknm = Workbooks("hilowwork book.xls").Worksheets
("UpdateInfo")

For rwIndex = lwrLimit To uprLimit

'This sets High Value of Option
With booknm.Cells(rwIndex, lgOpnHi)
If .Value _
< booknm.Cells(rwIndex, lgOpnHi - 1).Value _
Then .Value = booknm.Cells(rwIndex, lgOpnHi -
1).Value


--

John Green - Excel MVP
Sydney
Australia


"jfeka" wrote in message
...
John,

I've tried what you suggest and it produces an error
right off ... here's what I'm doing (below) What is
wrong?

Sub upDateHis1()

booknm = Workbooks("hilowwork book.xls").Worksheets
("UpdateInfo")
lwrLimit = Range("lwr").Row + 1
uprLimit = Range("eend").Row - 1
lgOpnHi = Range("OpnHi").Column
lgStokNow = Range("StokNow").Column
lgStokhi = lgStokNow + 1


For rwIndex = lwrLimit To uprLimit

'This sets High Value of Option
With Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi)
If .Value _
< Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi - 1).Value _
Then .Value = Worksheets
("UpdateInfo").Cells
(rwIndex, lgOpnHi - 1).Value
If .Value * 0.75 _
Worksheets("UpdateInfo").Cells(rwIndex,
lgOpnHi - 1).Value _
Then Worksheets("UpdateInfo").Cells
(rwIndex, 4).Value _
= "SELL"
End With
'This sets high value of Stock
If Worksheets("UpdateInfo").Cells(rwIndex,
lgStokNow).Value _
Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi).Value _
Then
Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi).Value _
= Worksheets("UpdateInfo").Cells(rwIndex,
lgStokNow).Value

Worksheets("UpdateInfo").Cells(rwIndex,
lgStokhi + 1).Value _
= Now
End If

Next rwIndex

rePeater

End Sub



-----Original Message-----
You can refer to cells in non-active workbooks by
fully
qualifying the references. Something like:

x = Workbooks("A.xls").Worksheets("Sheet1").Range
("A1").Value

If you need to make lots of references to the same
object, use the following:

With Workbooks("A.xls").Worksheets("Sheet1")
x = .Range("A1").Value
.Range("A2").Value = x

End With

--

John Green - Excel MVP
Sydney
Australia


"jfeka" wrote in message
...
Is there any way to get a macro to execute on a
workbook
which is open, but not active?

I have a macro in a certain workbook (call it "A")
which
repeats automatically but if I I open another
workbook,
say "B", then the macro produces an error because it
doesn't find the data it needs in the active
worksheet
if
I'm working in "B".

I've added the following code to the start of my
macro

Workbooks("hilowwork book.xls").Activate
Worksheets("UpdateInfo").Activate

and the macro runs fine but it causes the sheet to
become
active and it interrupts the work I may be doing in
workbook "B".

Is there some way I can feed the macro the
information
of
the workbook name and sheet name that it is to
operate
on
so that it does this in the background and still
allows
me to continue working in workbook "B" without
interruption?



.



.