I have the following code in a worksheet in an EXCEL 2003 file(call it
File1). It seems to work fine if I only have one EXCEL file open. If I have
another EXCEL file open(File2), the code seems to want to execute in that
file also. How to I restrict the code to File1?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("PJMdata!A1:A1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then



Range("PJMdata!J1:N1").Select
Selection.Copy
Range("PJMdata!Q2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("PJMdata!A1").Select



End If
End Sub

I don't see any reason why your code should affect another workbook. Since
I cleaned it up in looking it over I thought I'd post my equivalent code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
With Application
.EnableEvents = False
Range("J1:N1").Copy
Range("Q2").End(xlDown).Offset(1).PasteSpecial
Paste:=xlPasteValues
Range("A1").Select
.EnableEvents = True
.CutCopyMode = False
End With
End If
End Sub

Removed some unnecessary stuff like selecting cells. I added
Application.EnableEvents to keep the code itself from firing a change event.

--
Jim
"scrimmy" wrote in message
...
|I have the following code in a worksheet in an EXCEL 2003 file(call it
| File1). It seems to work fine if I only have one EXCEL file open. If I
have
| another EXCEL file open(File2), the code seems to want to execute in that
| file also. How to I restrict the code to File1?
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| Dim KeyCells As Range
|
| ' The variable KeyCells contains the cells that will
| ' cause an alert when they are changed.
| Set KeyCells = Range("PJMdata!A1:A1")
|
| If Not Application.Intersect(KeyCells, Range(Target.Address)) _
| Is Nothing Then
|
|
|
| Range("PJMdata!J1:N1").Select
| Selection.Copy
| Range("PJMdata!Q2").Select
| Selection.End(xlDown).Select
| ActiveCell.Offset(1, 0).Range("A1").Select
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
|
| Range("PJMdata!A1").Select
|
|
|
| End If
| End Sub

Jim,
Thanks for looking at this.
I tried using your code in my workbook and still have the problem of it
trying to execute in another workbook that I have open.

"Jim Rech" wrote:

I don't see any reason why your code should affect another workbook. Since
I cleaned it up in looking it over I thought I'd post my equivalent code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
With Application
.EnableEvents = False
Range("J1:N1").Copy
Range("Q2").End(xlDown).Offset(1).PasteSpecial
Paste:=xlPasteValues
Range("A1").Select
.EnableEvents = True
.CutCopyMode = False
End With
End If
End Sub

Removed some unnecessary stuff like selecting cells. I added
Application.EnableEvents to keep the code itself from firing a change event.

--
Jim
"scrimmy" wrote in message
...
|I have the following code in a worksheet in an EXCEL 2003 file(call it
| File1). It seems to work fine if I only have one EXCEL file open. If I
have
| another EXCEL file open(File2), the code seems to want to execute in that
| file also. How to I restrict the code to File1?
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| Dim KeyCells As Range
|
| ' The variable KeyCells contains the cells that will
| ' cause an alert when they are changed.
| Set KeyCells = Range("PJMdata!A1:A1")
|
| If Not Application.Intersect(KeyCells, Range(Target.Address)) _
| Is Nothing Then
|
|
|
| Range("PJMdata!J1:N1").Select
| Selection.Copy
| Range("PJMdata!Q2").Select
| Selection.End(xlDown).Select
| ActiveCell.Offset(1, 0).Range("A1").Select
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
|
| Range("PJMdata!A1").Select
|
|
|
| End If
| End Sub

still have the problem of it trying to execute in another workbook that I
have open.

Well, I' m sure not making this up but I have no idea how this can possibly
be happening (unless your other workbook is in fact another _window_ of the
same workbook. Nah<g).

--
Jim
"scrimmy" wrote in message
...
| Jim,
| Thanks for looking at this.
| I tried using your code in my workbook and still have the problem of it
| trying to execute in another workbook that I have open.
|
| "Jim Rech" wrote:
|
| I don't see any reason why your code should affect another workbook.
Since
| I cleaned it up in looking it over I thought I'd post my equivalent
code:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
| With Application
| .EnableEvents = False
| Range("J1:N1").Copy
| Range("Q2").End(xlDown).Offset(1).PasteSpecial
| Paste:=xlPasteValues
| Range("A1").Select
| .EnableEvents = True
| .CutCopyMode = False
| End With
| End If
| End Sub
|
| Removed some unnecessary stuff like selecting cells. I added
| Application.EnableEvents to keep the code itself from firing a change
event.
|
| --
| Jim
| "scrimmy" wrote in message
| ...
| |I have the following code in a worksheet in an EXCEL 2003 file(call it
| | File1). It seems to work fine if I only have one EXCEL file open. If
I
| have
| | another EXCEL file open(File2), the code seems to want to execute in
that
| | file also. How to I restrict the code to File1?
| |
| | Private Sub Worksheet_Change(ByVal Target As Range)
| | Dim KeyCells As Range
| |
| | ' The variable KeyCells contains the cells that will
| | ' cause an alert when they are changed.
| | Set KeyCells = Range("PJMdata!A1:A1")
| |
| | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
| | Is Nothing Then
| |
| |
| |
| | Range("PJMdata!J1:N1").Select
| | Selection.Copy
| | Range("PJMdata!Q2").Select
| | Selection.End(xlDown).Select
| | ActiveCell.Offset(1, 0).Range("A1").Select
| | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| | SkipBlanks _
| | :=False, Transpose:=False
| |
| | Range("PJMdata!A1").Select
| |
| |
| |
| | End If
| | End Sub
|
|
|

I'm sure YOU'RE not making

--
Jim
"Jim Rech" wrote in message
...
| still have the problem of it trying to execute in another workbook that
I
| have open.
|
| Well, I' m sure not making this up but I have no idea how this can
possibly
| be happening (unless your other workbook is in fact another _window_ of
the
| same workbook. Nah<g).
|
| --
| Jim
| "scrimmy" wrote in message
| ...
|| Jim,
|| Thanks for looking at this.
|| I tried using your code in my workbook and still have the problem of it
|| trying to execute in another workbook that I have open.
||
|| "Jim Rech" wrote:
||
|| I don't see any reason why your code should affect another workbook.
| Since
|| I cleaned it up in looking it over I thought I'd post my equivalent
| code:
||
|| Private Sub Worksheet_Change(ByVal Target As Range)
|| If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
|| With Application
|| .EnableEvents = False
|| Range("J1:N1").Copy
|| Range("Q2").End(xlDown).Offset(1).PasteSpecial
|| Paste:=xlPasteValues
|| Range("A1").Select
|| .EnableEvents = True
|| .CutCopyMode = False
|| End With
|| End If
|| End Sub
||
|| Removed some unnecessary stuff like selecting cells. I added
|| Application.EnableEvents to keep the code itself from firing a change
| event.
||
|| --
|| Jim
|| "scrimmy" wrote in message
|| ...
|| |I have the following code in a worksheet in an EXCEL 2003 file(call it
|| | File1). It seems to work fine if I only have one EXCEL file open.
If
| I
|| have
|| | another EXCEL file open(File2), the code seems to want to execute in
| that
|| | file also. How to I restrict the code to File1?
|| |
|| | Private Sub Worksheet_Change(ByVal Target As Range)
|| | Dim KeyCells As Range
|| |
|| | ' The variable KeyCells contains the cells that will
|| | ' cause an alert when they are changed.
|| | Set KeyCells = Range("PJMdata!A1:A1")
|| |
|| | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
|| | Is Nothing Then
|| |
|| |
|| |
|| | Range("PJMdata!J1:N1").Select
|| | Selection.Copy
|| | Range("PJMdata!Q2").Select
|| | Selection.End(xlDown).Select
|| | ActiveCell.Offset(1, 0).Range("A1").Select
|| | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
|| | SkipBlanks _
|| | :=False, Transpose:=False
|| |
|| | Range("PJMdata!A1").Select
|| |
|| |
|| |
|| | End If
|| | End Sub
||
||
||
|
|

Thanks again for looking at this. The other workbook is NOT another window
of the same workbook. Could the problem be caused by the web query that I am
using to refresh the data in cell A1? I have the web query set up to run
every 5 minutes and if the value it returns to cell A1 has changed, the code
will execute. As I've said, it works fine unless I am working in another
workbook.

"Jim Rech" wrote:

I'm sure YOU'RE not making

--
Jim
"Jim Rech" wrote in message
...
| still have the problem of it trying to execute in another workbook that
I
| have open.
|
| Well, I' m sure not making this up but I have no idea how this can
possibly
| be happening (unless your other workbook is in fact another _window_ of
the
| same workbook. Nah<g).
|
| --
| Jim
| "scrimmy" wrote in message
| ...
|| Jim,
|| Thanks for looking at this.
|| I tried using your code in my workbook and still have the problem of it
|| trying to execute in another workbook that I have open.
||
|| "Jim Rech" wrote:
||
|| I don't see any reason why your code should affect another workbook.
| Since
|| I cleaned it up in looking it over I thought I'd post my equivalent
| code:
||
|| Private Sub Worksheet_Change(ByVal Target As Range)
|| If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
|| With Application
|| .EnableEvents = False
|| Range("J1:N1").Copy
|| Range("Q2").End(xlDown).Offset(1).PasteSpecial
|| Paste:=xlPasteValues
|| Range("A1").Select
|| .EnableEvents = True
|| .CutCopyMode = False
|| End With
|| End If
|| End Sub
||
|| Removed some unnecessary stuff like selecting cells. I added
|| Application.EnableEvents to keep the code itself from firing a change
| event.
||
|| --
|| Jim
|| "scrimmy" wrote in message
|| ...
|| |I have the following code in a worksheet in an EXCEL 2003 file(call it
|| | File1). It seems to work fine if I only have one EXCEL file open.
If
| I
|| have
|| | another EXCEL file open(File2), the code seems to want to execute in
| that
|| | file also. How to I restrict the code to File1?
|| |
|| | Private Sub Worksheet_Change(ByVal Target As Range)
|| | Dim KeyCells As Range
|| |
|| | ' The variable KeyCells contains the cells that will
|| | ' cause an alert when they are changed.
|| | Set KeyCells = Range("PJMdata!A1:A1")
|| |
|| | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
|| | Is Nothing Then
|| |
|| |
|| |
|| | Range("PJMdata!J1:N1").Select
|| | Selection.Copy
|| | Range("PJMdata!Q2").Select
|| | Selection.End(xlDown).Select
|| | ActiveCell.Offset(1, 0).Range("A1").Select
|| | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
|| | SkipBlanks _
|| | :=False, Transpose:=False
|| |
|| | Range("PJMdata!A1").Select
|| |
|| |
|| |
|| | End If
|| | End Sub
||
||
||
|
|

I don't do many web queries so I don't know if it might be causing this.

If you have another workbook open and manually make an entry in A1 does the
problem occur?

--
Jim
"scrimmy" wrote in message
...
Thanks again for looking at this. The other workbook is NOT another
window
of the same workbook. Could the problem be caused by the web query that I
am
using to refresh the data in cell A1? I have the web query set up to run
every 5 minutes and if the value it returns to cell A1 has changed, the
code
will execute. As I've said, it works fine unless I am working in another
workbook.

"Jim Rech" wrote:

I'm sure YOU'RE not making

--
Jim
"Jim Rech" wrote in message
...
| still have the problem of it trying to execute in another workbook
that
I
| have open.
|
| Well, I' m sure not making this up but I have no idea how this can
possibly
| be happening (unless your other workbook is in fact another _window_ of
the
| same workbook. Nah<g).
|
| --
| Jim
| "scrimmy" wrote in message
| ...
|| Jim,
|| Thanks for looking at this.
|| I tried using your code in my workbook and still have the problem of
it
|| trying to execute in another workbook that I have open.
||
|| "Jim Rech" wrote:
||
|| I don't see any reason why your code should affect another workbook.
| Since
|| I cleaned it up in looking it over I thought I'd post my equivalent
| code:
||
|| Private Sub Worksheet_Change(ByVal Target As Range)
|| If Not Application.Intersect(Range("A1"), Target) Is Nothing
Then
|| With Application
|| .EnableEvents = False
|| Range("J1:N1").Copy
|| Range("Q2").End(xlDown).Offset(1).PasteSpecial
|| Paste:=xlPasteValues
|| Range("A1").Select
|| .EnableEvents = True
|| .CutCopyMode = False
|| End With
|| End If
|| End Sub
||
|| Removed some unnecessary stuff like selecting cells. I added
|| Application.EnableEvents to keep the code itself from firing a
change
| event.
||
|| --
|| Jim
|| "scrimmy" wrote in message
|| ...
|| |I have the following code in a worksheet in an EXCEL 2003 file(call
it
|| | File1). It seems to work fine if I only have one EXCEL file open.
If
| I
|| have
|| | another EXCEL file open(File2), the code seems to want to execute
in
| that
|| | file also. How to I restrict the code to File1?
|| |
|| | Private Sub Worksheet_Change(ByVal Target As Range)
|| | Dim KeyCells As Range
|| |
|| | ' The variable KeyCells contains the cells that will
|| | ' cause an alert when they are changed.
|| | Set KeyCells = Range("PJMdata!A1:A1")
|| |
|| | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
|| | Is Nothing Then
|| |
|| |
|| |
|| | Range("PJMdata!J1:N1").Select
|| | Selection.Copy
|| | Range("PJMdata!Q2").Select
|| | Selection.End(xlDown).Select
|| | ActiveCell.Offset(1, 0).Range("A1").Select
|| | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
|| | SkipBlanks _
|| | :=False, Transpose:=False
|| |
|| | Range("PJMdata!A1").Select
|| |
|| |
|| |
|| | End If
|| | End Sub
||
||
||
|
|

I believe have found a way around my problem. It seems that if I want to
work with other EXCEL files while the file in question is open, I have to
physically open a separate session of EXCEL. This is a bit of nuisance and
doesn't answer the question of why the code tries to run in other files, but
it gets me past my problem. Thank you again for looking into this. Your
help is greatly appreciated.

"Jim Rech" wrote:

I don't do many web queries so I don't know if it might be causing this.

If you have another workbook open and manually make an entry in A1 does the
problem occur?

--
Jim
"scrimmy" wrote in message
...
Thanks again for looking at this. The other workbook is NOT another
window
of the same workbook. Could the problem be caused by the web query that I
am
using to refresh the data in cell A1? I have the web query set up to run
every 5 minutes and if the value it returns to cell A1 has changed, the
code
will execute. As I've said, it works fine unless I am working in another
workbook.

"Jim Rech" wrote:

I'm sure YOU'RE not making

--
Jim
"Jim Rech" wrote in message
...
| still have the problem of it trying to execute in another workbook
that
I
| have open.
|
| Well, I' m sure not making this up but I have no idea how this can
possibly
| be happening (unless your other workbook is in fact another _window_ of
the
| same workbook. Nah<g).
|
| --
| Jim
| "scrimmy" wrote in message
| ...
|| Jim,
|| Thanks for looking at this.
|| I tried using your code in my workbook and still have the problem of
it
|| trying to execute in another workbook that I have open.
||
|| "Jim Rech" wrote:
||
|| I don't see any reason why your code should affect another workbook.
| Since
|| I cleaned it up in looking it over I thought I'd post my equivalent
| code:
||
|| Private Sub Worksheet_Change(ByVal Target As Range)
|| If Not Application.Intersect(Range("A1"), Target) Is Nothing
Then
|| With Application
|| .EnableEvents = False
|| Range("J1:N1").Copy
|| Range("Q2").End(xlDown).Offset(1).PasteSpecial
|| Paste:=xlPasteValues
|| Range("A1").Select
|| .EnableEvents = True
|| .CutCopyMode = False
|| End With
|| End If
|| End Sub
||
|| Removed some unnecessary stuff like selecting cells. I added
|| Application.EnableEvents to keep the code itself from firing a
change
| event.
||
|| --
|| Jim
|| "scrimmy" wrote in message
|| ...
|| |I have the following code in a worksheet in an EXCEL 2003 file(call
it
|| | File1). It seems to work fine if I only have one EXCEL file open.
If
| I
|| have
|| | another EXCEL file open(File2), the code seems to want to execute
in
| that
|| | file also. How to I restrict the code to File1?
|| |
|| | Private Sub Worksheet_Change(ByVal Target As Range)
|| | Dim KeyCells As Range
|| |
|| | ' The variable KeyCells contains the cells that will
|| | ' cause an alert when they are changed.
|| | Set KeyCells = Range("PJMdata!A1:A1")
|| |
|| | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
|| | Is Nothing Then
|| |
|| |
|| |
|| | Range("PJMdata!J1:N1").Select
|| | Selection.Copy
|| | Range("PJMdata!Q2").Select
|| | Selection.End(xlDown).Select
|| | ActiveCell.Offset(1, 0).Range("A1").Select
|| | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
|| | SkipBlanks _
|| | :=False, Transpose:=False
|| |
|| | Range("PJMdata!A1").Select
|| |
|| |
|| |
|| | End If
|| | End Sub
||
||
||
|
|