PasteSpecial xlPasteFormats ends macro

[itarnak]

On Excel Office 2000 SP3 / VBA 6.0 :

Here is the problem:

1. Initial problem

I want to copy rows (values+formats) from a sheet to another sheet, in the same workbook.
I make a loop, to choose rows (no all rows are ok, i test some cells values). It does something like that:


in a loop (for):

SourceSheet.Rows(sourceRowNumber).Copy
DestinationSheet.Rows(destinationRowNumber).PasteSpecial Paste:=xlPasteValues
DestinationSheet.Rows(destinationRowNumber).PasteSpecial Paste:=xlPasteFormats


Results:

Only the first line is copied (values+formats), the macro ends after the first xlPasteFormats paste procedure call, and it waits for the user to manualy select a destination cells, and when i do it, and strike enter, it copies again values of this line..
What does that mean??


2. Second test:

I delete the line "DestinationSheet.Rows(destinationRowNumber).PasteSpecial Paste:=xlPasteFormats" from the above loop.

Results: it works fine, the whole macro is executed, i have all values ...but i have no formats...


3. Third test:

Instead of making a loop, i copy the whole sheet (values+formats):

SourceSheet.UsedRange.Copy
DestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
DestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

...

MsgBox "After copy..."

...

Results: same as 1. : the macro ends after the xlPasteFormats paste procedure call, MsgBox are never executed: all values and formats are copied, but it waits for the user to select a destination cell and when done copies again the whole sheet ....



So what does that mean?

iTarnak

[itarnak]

Without the "Paste" procedure call with xlPasteValues (only xlPasteFormats):

1. The formats are copied
2. Excel waits for user action: i select manually destination cell, strike enter, and then the values are copied too....and the macro ends immediatly...


I don't understand that....


Regards,

iTarnak

[Jim Rech]

>>I don't understand that....

Nor do I. There is no way a paste special of formats should make a macro
pause and wait for user input. Have you tried running this code (or a
simplified version of it) in a new workbook? If it runs as it should the
problem workbook might have "gone bad", i.e., have some kind of corruption.
That sounds lame but I've seen workbooks start doing weirder things than
this for no apparent reason.

--
Jim
"itarnak" <[email protected]> wrote in
message news:[email protected]...
|
| Without the "Paste" procedure call with xlPasteValues (only
| xlPasteFormats):
|
| 1. The formats are copied
| 2. Excel waits for user action: i select manually destination cell,
| strike enter, and then the values are copied too....and the macro ends
| immediatly...
|
|
| I don't understand that....
|
|
| Regards,
|
| iTarnak
|
|
| --
| itarnak
| ------------------------------------------------------------------------
| itarnak's Profile:
http://www.excelforum.com/member.php...o&userid=27865
| View this thread: http://www.excelforum.com/showthread...hreadid=473725
|

[itarnak]

I have just discovered that if i manually copy a row in that source sheet and paste it elsewhere, once pasted, excel asked me again to select destination cell and strike enter.......and when i do it it pastes again the row.....

What is this strange behaviour??

Regards,

iTarnak

[itarnak]

The pb was due to a date which were in a bad format (not a format date), and there were an format condition on this column. The conditional format called a macro to calculate difference between dates. As there were a bad date in one cell, instead of showing error when calling xlPasteFormats , excel did nothing, waiting for user action (manualy paste) since it just called the copy function before.

Thats all...