Force destination formatting

**krumel** · 05-13-2013, 01:32 AM

Hi,
sorry for this noob question but I'm searching thru the Internet and this forum for quite a while now..Without any results.. (Or I did not regarded them as results)

Situation:
I have a spread sheet in Excel 2010 where the user should copy and paste data from a IE window into.
I wanted to have a Macro that automatically chooses "Match destination formatting" when CTRL+V is used to paste.

The code:

Please Login or Register  to view this content.

The problem:
This works quite well when the user is actually pasting from the IE.
BUT: If the user uses CTRL+V for any other paste (e.g. to paste a cell or anything) it produces a
"Run-time-error 1004"
"PasteSpecial method for Worksheet class has failed"

I must admit that it goes beyond my skills to fix this...
Any advice how to solve this problem would be highly regarded.

Kind regards,
Krumel

**wenqq3** · 05-13-2013, 01:45 AM

Change your shortcut Key to another?
View Macros > Select your macro > option

**krumel** · 05-13-2013, 10:04 PM

Sadly this is not an option...It already was a nightmare to explain copy and paste to the users (I'm working with "best agers" volunteers)...There must be a VBA solution somewhere.

**krumel** · 05-13-2013, 10:20 PM

I think I found a different approach.

Please Login or Register  to view this content.

If this is used inside the workbook it should keep the formatting.

**krumel** · 05-13-2013, 11:31 PM

The solution above has two problems I meanwhile found out:
1. The performance is poor
2. It crashes if it can not paste e.g. if the user tries to paste a value in a protected cell, etc.

Therefor I keep this open.