I have a macro that copies a range from one workbook, opens another workbook,
selects a sheet and then pastes it. Part of the code looks like this:

ChDir "C:\"
Workbooks.Open Filename:="C:\Temporary.xls"
Sheets("Sizes").Select
Range("A3").Select
ActiveSheet.Paste

It used to work. I can say that what I have changed is that I created a
Change Event in the Workbook called "Temporary". Is it getting confused by
that somehow? Thank you for your help.
--
David P.

Do you really mean to copy A3 to the activesheet?

I bet that the activesheet is Sizes in Temporary.xls. And when you paste, it
fires the event.

I'd use:

dim TempWkbk as workbook
dim ActCell as Range

'where the cursor is right now!
set ActCell = Activecell

'I don't think you'll need this now
'application.enableevents = false

set tempwkbk = workbooks.open(filename:="C:\temporary.xls")
tempwkbk.worksheets("sizes").range("a3").copy _
destination:=actcell

tempwkbk.close savechanges:=false

'if you used that line from before, turn events back on
'application.enableevents = true




David P. wrote:

I have a macro that copies a range from one workbook, opens another workbook,
selects a sheet and then pastes it. Part of the code looks like this:

ChDir "C:\"
Workbooks.Open Filename:="C:\Temporary.xls"
Sheets("Sizes").Select
Range("A3").Select
ActiveSheet.Paste

It used to work. I can say that what I have changed is that I created a
Change Event in the Workbook called "Temporary". Is it getting confused by
that somehow? Thank you for your help.
--
David P.

--

Dave Peterson

Hello Dave,

I didn't give you the full code because it is pretty long but basically
before

ChDir "C:\"
Workbooks.Open Filename:="C:\Temporary.xls"

the range had already been selected from another workbook and is being
pasted in Sheet "Sizes" cell A3 in "temporary.xls".

It's getting stuck at:

Sheets("Sizes").Select

It was working before I put the change events into "Temporary.xls". Might it
have something to do with where the macros are stored that is confusing it
and creating the error? I sure hope I'm communicating myself. Let me know if
you need to see more of the code. Thank you.

--
David P.


"Dave Peterson" wrote:

Do you really mean to copy A3 to the activesheet?

I bet that the activesheet is Sizes in Temporary.xls. And when you paste, it
fires the event.

I'd use:

dim TempWkbk as workbook
dim ActCell as Range

'where the cursor is right now!
set ActCell = Activecell

'I don't think you'll need this now
'application.enableevents = false

set tempwkbk = workbooks.open(filename:="C:\temporary.xls")
tempwkbk.worksheets("sizes").range("a3").copy _
destination:=actcell

tempwkbk.close savechanges:=false

'if you used that line from before, turn events back on
'application.enableevents = true




David P. wrote:

I have a macro that copies a range from one workbook, opens another workbook,
selects a sheet and then pastes it. Part of the code looks like this:

ChDir "C:\"
Workbooks.Open Filename:="C:\Temporary.xls"
Sheets("Sizes").Select
Range("A3").Select
ActiveSheet.Paste

It used to work. I can say that what I have changed is that I created a
Change Event in the Workbook called "Temporary". Is it getting confused by
that somehow? Thank you for your help.
--
David P.

--

Dave Peterson

If you're pasting into that workbook, then the worksheet_change event will fire.

dim TempWkbk as workbook
application.enableevents = false
set tempwkbk = workbooks.open(filename:="C:\temporary.xls")
application.enableevents = false
tempwkbk.worksheets("sizes").range("a3").paste
application.enableevents = true
tempwkbk.close savechanges:=true

=======
I'd actually do something like:

dim TempWkbk as workbook
Dim RngToCopy as range

set rngtocopy = workbooks("someworkbook.xls") _
.worksheets("somesheet").range("somerange")

application.enableevents = false
set tempwkbk = workbooks.open(filename:="C:\temporary.xls")
application.enableevents = false
rngtocopy.copy _
destination:=tempwkbk.worksheets("sizes").range("a 3")
application.enableevents = true
tempwkbk.close savechanges:=true

========
Someday, you may find that opening the workbook will empty the clipboard.
Copying right before the paste is not a bad way to go.




David P. wrote:

Hello Dave,

I didn't give you the full code because it is pretty long but basically
before

ChDir "C:\"
Workbooks.Open Filename:="C:\Temporary.xls"

the range had already been selected from another workbook and is being
pasted in Sheet "Sizes" cell A3 in "temporary.xls".

It's getting stuck at:

Sheets("Sizes").Select

It was working before I put the change events into "Temporary.xls". Might it
have something to do with where the macros are stored that is confusing it
and creating the error? I sure hope I'm communicating myself. Let me know if
you need to see more of the code. Thank you.

--
David P.

"Dave Peterson" wrote:

Do you really mean to copy A3 to the activesheet?

I bet that the activesheet is Sizes in Temporary.xls. And when you paste, it
fires the event.

I'd use:

dim TempWkbk as workbook
dim ActCell as Range

'where the cursor is right now!
set ActCell = Activecell

'I don't think you'll need this now
'application.enableevents = false

set tempwkbk = workbooks.open(filename:="C:\temporary.xls")
tempwkbk.worksheets("sizes").range("a3").copy _
destination:=actcell

tempwkbk.close savechanges:=false

'if you used that line from before, turn events back on
'application.enableevents = true




David P. wrote:

I have a macro that copies a range from one workbook, opens another workbook,
selects a sheet and then pastes it. Part of the code looks like this:

ChDir "C:\"
Workbooks.Open Filename:="C:\Temporary.xls"
Sheets("Sizes").Select
Range("A3").Select
ActiveSheet.Paste

It used to work. I can say that what I have changed is that I created a
Change Event in the Workbook called "Temporary". Is it getting confused by
that somehow? Thank you for your help.
--
David P.

--

Dave Peterson


--

Dave Peterson