0

I am having an issue where data is not being consistently pasted into my spreadsheet from an outside program. I have 10 data points that should be pasted into 10 cells, but sometimes I will be missing some. I recently found that all of the values are being copied to the Office clipboard, I am looking to modify my current macro (below) to check each cell in the row for if it is blank and to then paste the correct value from the clipboard. I would then like to clear the clipboard so that the code is consistent for each row.

How can I paste multiple/specific items from the clipboard and also how can I clear it afterwards. I have scoured stack overflow and many other Excel/VBA forums but none of them have a question/answer for clearing the OFFICE clipboard in office 365.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculation = xlCalculationManual
    Dim MyRange As Range
    Dim CurrentRange As Range
    Dim currentrow As Integer
 
    Set MyRange = Range("R56:R1056")

    If Not Intersect(Target, MyRange) Is Nothing Then

        currentrow = ActiveCell.Row     ' Find the current row in integer format (i.e. 6)
        Columnstart = "C" & currentrow  ' Collate into the start column (i.e. C6)
        Columnend = "P" & currentrow    ' Collate into the End column (i.e. M6)
        ' Print values for verification
        ' MsgBox "Current row: " & currentrow & vbNewLine & "Column Start Value 'C': " & Columnstart & vbNewLine & "End Column Value 'M': " & Columnend
      
        Set CurrentRange = Range(Columnstart & ":" & Columnend)
        'MsgBox CurrentRange ' Check for current range (i.e. C6:M6)
        DataCells = WorksheetFunction.Count(CurrentRange)
        'MsgBox blanks ' Check for how many
        If DataCells < 10 Then
            ' Set the cell value to missing and color to Red
            Range("Q" & ActiveCell.Row).Interior.ColorIndex = 3
            Range("Q" & ActiveCell.Row).Value = "Incomplete"
        Else
            ' Set the cell value to complete then color it green
            Range("Q" & ActiveCell.Row).Interior.ColorIndex = 4
            Range("Q" & ActiveCell.Row).Value = "Complete"
        End If
        Range("C" & ActiveCell.Row + 1).Select ' Move to beginning of next row for next data input
        Calculate
    End If
End Sub

Thanks,

Office 365 Ver2102 (Build 13801.20864) | Windows 10 Enterprise (Build 19042.1165 )

PeterT
  • 8,232
  • 1
  • 17
  • 38
Bormas
  • 1
  • 2
  • How about `Application.CutCopyMode=False `, from [this site](https://a4accounting.com.au/excel-vba-command-to-clear-the-clipboard/) – PeterT Aug 24 '21 at 16:48
  • @PeterT I had tried that, but later removed it as it only clears the excel clipboard and not the office one. I have also been trying to work through [This One](https://stackoverflow.com/questions/59322818/clearing-office-clipboard-is-not-working-after-office-365-update) but I am having trouble understanding everything – Bormas Aug 24 '21 at 17:39

0 Answers0