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
||
||
||
|
|