Application Defined error

[Guest]

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.

 

[Dave Peterson]

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

 

[Guest]

Hello Dave,

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

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:

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.

 

[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("a3")
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.

Hello Dave,

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

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:

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.