Run a macro before paste

[Guest]

Is it possible to run a macro automatically before every paste? I need to
paste information from another program into a merged cell (I don't like them
either, but I need them for the sheet), and I keep getting the error message
about pasting into different sized sheets.

If I can't do that, can you run a command button on a mouse click while
another cell is active(not sure if that is the right term, what I mean is
when you select a cell and hit F2 to edit the content, it has the flashing
marker)

Thanks,

David

 

[NickHK]

David,
There's no _BeforePaste event. What you can achieve will depend on how much
control you have and what you are trying to do.

What is the other "application" ?
If it is not Excel, you may be limited to text and the DataObject.

Whilst macros do not run when the user is in Edit Mode (F2), you could maybe
fake something with SendKeys, although using .Characters may also work.

I did notice something, that for me, was a little strange, with the
different results of these 2 quick recorded macros:

Sub PasteToMerge_NotGood()

Range("A1:A4").Select
With Selection
.MergeCells = True
End With

Sheets("Sheet2").Select
Range("C2:E2").Select
With Selection
.MergeCells = True
End With

Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "45"
Range("A1:A4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C2:E2").Select
ActiveSheet.Paste
Range("C8").Select
End Sub

Sub PasteToMerge_Good()

Range("A1:A4").Select
Application.CutCopyMode = False
With Selection
.MergeCells = True
End With
Sheets("Sheet2").Select
'
'This is the only difference
'Creates a merged range same size as the copied range
'But it is not used at all....
'
Range("A1:A4").Select
With Selection
.MergeCells = True
End With
'
Range("C2:E2").Select
With Selection
.MergeCells = True
End With
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "45"
Range("A1:A4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C2:E2").Select
ActiveSheet.Paste
'See the different result...
Range("C4").Select
End Sub
Maybe someone else can explain the difference in behaviour, depending on if
the destination sheet is the same as the source sheet or not. And the
existence of a similar shaped merged range on the destination

So maybe if you copied to the sheet (in a suitable out of the way place)
first that contains the destination, then copy that and paste to the real
destination, you can achieve your goal.

But anyway, if the above does not give you any ideas, post back with more
details of the aim.

NickHK