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
message |
| 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?action=getinfo&userid=27865
| View this thread: http://www.excelforum.com/showthread.php?threadid=473725
|

 

[itarnak]

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

What is this strange behaviour??

Regards,

iTarna

 

[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 conditiona
format called a macro to calculate difference between dates. As ther
were a bad date in one cell, instead of showing error when callin
xlPasteFormats , excel did nothing, waiting for user action (manual
paste) since it just called the copy function before.

Thats all..