rjplante
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 569
- Office Version
- 365
- Platform
- Windows
I have a protected worksheet that has cells B2 and B4 unlocked. I have a macro below which should check to make sure the clipboard is not blank, notify the user if it is, and paste previously copied values from a web site or an email into cell B2. Whenever I try to paste the values using the macro I get an error (Run-time error '1004': PasteSpecial method of range class failed). I can simply use the paste function using the Ctrl+V keyboard command and it will paste the text without any trouble, except that once I hit paste, it will automatically move to cell B4, and cell B2 becomes locked, the border on the bottom is removed and the text is not aligned in the center anymore. I don't know what is going on. Here is what I need help with:
1) Getting the paste values macro to work.
2) Getting the paste via the keyboard command to work without locking cell B2, then removing all formatting of cell B2 (stripping the border and center alignment of the text).
I appreciate all the help.
BTW - I am running Excel 2016
1) Getting the paste values macro to work.
2) Getting the paste via the keyboard command to work without locking cell B2, then removing all formatting of cell B2 (stripping the border and center alignment of the text).
I appreciate all the help.
BTW - I am running Excel 2016
Code:
Sub Paste_Values_Timeout()
Sheets("FDN Timeout Parameters").Range("B2").Select
Dim myDataObject As DataObject
Set myDataObject = New DataObject
myDataObject.GetFromClipboard
If myDataObject.GetFormat(1) = True Then
Sheets("FDN Timeout Parameters").Range("B2").Select
Selection.PasteSpecial xlPasteValues
Else
MsgBox "There is no data on the clipboard to paste into the sheet"
End If
End Sub