Stop Macro based on Range

[Darth Metal]

Hello,

I have a simple paste special (values) macro, the data that is to be pasted runs from column AI to AX. In Cell BA I have some formulas that take what I need from the pasted data. As I have other people pasting the data, I would like to know if there is a code that will prevent the user from overwriting the formulas to the right of cell AX (ie someone has copied the wrong report and tries to paste it in the wrong area).

I would like a custom error message to pop up if when someone runs the macro when the result would exceed the AI:AX range and prevent the data from being pasted.

Any help would be greatly appreciated.

Thank you

OS: XP
Office 2010

Existing Macro:
Range("AI3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[ChrisM]

I started looking in to those, and the first path I went down is since you have already selected the range to be copied, the only way to check the size of it is to look at what's in the clipboard. You CAN view the clipboard in VBA, but the results are messy. It comes out as a text string, and I can't see how it is formatted/delimited. I think there is more data stored on the clipboard than we have access to, because for example there is nothing to tell you it is a Range vs. just plain old text.

I think it would be A LOT easier if you attempted to monitor/control the initial selection of the data, rather than try to respond after the fact.

An ugly workaround would be to first paste the data to a hidden empty sheet, then test the column count. If it matches then delete the test data and perform the paste for real in the actual sheet.