PDA

View Full Version : Solved: Copy works, copy/paste doesn't... (bug?)



chamster
09-27-2007, 07:04 AM
This works well.

.Range(.Cells(2, 1), .Cells(tSpan + 1, 1)).Copy Destination:=.Cells(2, rSpan + 3)


While this doesn't. Error 1004.

.Range(.Cells(2, 1), .Cells(tSpan + 1, 1)).Copy
.Cells(2, rSpan + 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

What can be the reason? When i step through the code, i see the copy segment to be copied. Then i get the error as i try to paste it in. When i skipped Paste:= and the other parameters, i got no error but nothing was copied. Also, i believe that it sometimes actually goes through and sometimes doesn't. I don't see any general reason for why. What's up here?

rory
09-27-2007, 07:40 AM
Any merged cells or sheet protection?

chamster
09-27-2007, 10:13 AM
Nope. It was my thought too but nope...

Anyway - it's so weird that copy directly works but copy/pastespecial doesn't. Weirdest thing...

Bob Phillips
09-27-2007, 10:43 AM
Why not post the workbook to see.

Oorang
09-27-2007, 10:46 AM
Are there any interveaning lines of code posted or triggered evetns that might be clearing the clipboard between the copy and the paste?

chamster
09-27-2007, 01:27 PM
Are there any interveaning lines of code posted or triggered evetns that might be clearing the clipboard between the copy and the paste?
No, there are not. Those are the actual lines of code executed right after eachother. No event triggering that i snap up and control either.

I would post an example but i don't think it's a good idea because the whole shabang is rather large and i'd hate to strip it to a smaller example. I was hoping for something simplier, such as "can't do pastespecial on .cell, use .range, dumbass!".

Come to think of it - is it possible to see the contents of the clipboard in the debugger? Like watching a variable, i mean. That way, i could see exactly what's in there. On the other hand, even if it's empty, i shouldn't get errors pasting it in.

johnske
09-27-2007, 06:01 PM
What office version are you using? xlPasteValuesAndNumberFormats didn't come out until 2002, so if you're using office 2000 it will give an error - you'd then need to do a double paste with Pastespecial xlvalues then Pastespecial xlFormats

chamster
09-27-2007, 09:09 PM
I'm sitting on ver. 2003. I have tested just that syntax on a very short demo version and i noticed that it does work. So, the problem is elsewhere than sytax. I have no idea where though. Even worse - i don't know how to disect the problem, either. As far i understand, error 1004 is VB's way of saying "i dunno, buddy".

Your expert knowledge is of greatest need on this one.

johnske
09-27-2007, 09:24 PM
if that does what you want then why not use it? it's much safer as it then has backwards compatibility if you should distribute to someone with office 2000 (you should always develop on the lowest version for compatibility reasons)

chamster
09-27-2007, 10:17 PM
I tested to run the code. Of, course, i got errors. Then, i put a breakpoint just before the pastespecial-part. I went to excel during that hold-up and tried specialpaste from the menus. It worked perfectly. Above that, when i F8'ed the code it didn't produce errors. It pasted stuff in just fine!

If i don't do the manual pasting in (of course, in a different place, so i can see the difference, hehe) i get the error. This is very mistical to me. Any bright head haveing any thoughts?

chamster
09-27-2007, 10:20 PM
if that does what you want then why not use it? it's much safer as it then has backwards compatibility if you should distribute to someone with office 2000 (you should always develop on the lowest version for compatibility reasons)

I want to break this problem because i want to understand what i did wrong. I have a workaround (luckily) but next time, perhaps i won't. I prefer to solve a problem before it happens.

Thanks for the hint on the compatibility issue. The DMV here has 2003 or later as standard so my brown eye is covered. Keep helping! :)

chamster
09-27-2007, 10:47 PM
I believe i've located the source of the problem.

There's a chart on the current sheet. When it's selected, i get the errors. If i deselect it (by e.g. pasting in something but also by clicking anywhere in the sheet) i pass errorlessly.

Since i've used with Worksheets("yoMoma") i though i was protected and .Range("blopp") would refer to a cell. It seems as it doesn't, however.

So, i only need to deactivate the chart, i'd guess but there's no .Deactivate for charts, only for sheets. Howdo i do it? RIght now i'm using this so-so greatly conceived below.

.Activate
.Cells(1, 1).Select
.Range(.Cells(2, 1), .Cells(tSpan + 1, 1)).Copy
.Cells(2, rSpan + 3).PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlPasteSpecialOperationNone


It basically simulates a user clicking in a cell. That's a working but hardly good programming, in my opinion.

johnske
09-27-2007, 11:03 PM
that's quite an acceptable solution...

chamster
09-27-2007, 11:31 PM
Thank you. However, now that the issue of the application not working is gone, we have the issue of perfection. I feel it should be done in a more proffesional way. (Yes, i know, i'm a pain-in-between-perfectionist but on the other hand, somebody's got to be the best, so why not me, right? :) )

johnske
09-28-2007, 04:49 PM
another way around this is to just stop the user selecting the chart - by protecting the worksheet that has the chart in it

chamster
09-28-2007, 11:44 PM
The problem here is that the selection occurs during the creation of the chart (or perhaps placement on the final worksheet). So there's really no user to prevent from clicking, so to speak.

However, perhaps you could suggest a way to create/place a chart without activating it (or deactivating right afterwards)? Other than ActiveSheet.Cells(1,1).Select, that is.

johnske
09-29-2007, 12:00 AM
The problem here is that the selection occurs during the creation of the chart (or perhaps placement on the final worksheet). So there's really no user to prevent from clicking, so to speak.

However, perhaps you could suggest a way to create/place a chart without activating it (or deactivating right afterwards)? Other than ActiveSheet.Cells(1,1).Select, that is.well if that's the case, after creating the chart and doing what you want with it, use ActiveChart.Deselect

johnske
09-29-2007, 12:20 AM
BTW, giving a bit more info about your problem would probably have got you a solution from someone much sooner. Knowing that a chart has been added immediately prior to your copy/paste problem is a pretty crucial bit of info :)

chamster
10-01-2007, 05:41 AM
BTW, giving a bit more info about your problem would probably have got you a solution from someone much sooner. Knowing that a chart has been added immediately prior to your copy/paste problem is a pretty crucial bit of info :)

I didn't realize the cruciality of that info, sorry. I'll try to remember that to the next time and since i'll most likely miss that again - sorry in advance.

johnske
10-01-2007, 05:53 AM
well that's why mostly we prefer to see an attached workbook, but if that's not possible ... the whole procedure, not just the immediate part it crashes on :)