VBA, Copy, Remove Ants, Keep Clipboard

[bepedicino]

I have the following VBA which copies a dynamic range. However, I want to clear the marching ants, but retain the clipboard so I can paste into another application. Can anyone assist?

Sub CopyData()
Dim a As Integer
    a = Sheets("Classification").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Classification").Range("A1:I" & a).Copy
   
    Application.CutCopyMode = True
   
    ActiveSheet.Range("A2").Select
   
End Sub

 

[Yongle]

so I can paste into another application

Unrefined example to paste into Word

Sub PasteToWord()
    Dim a As Integer
    a = Sheets("Classification").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Classification").Range("A1:I" & a).Copy
    With CreateObject("Word.Application")
        .Visible = True
        .documents.Add
        .Selection.Paste
    End With
    Application.CutCopyMode = True
End Sub

Paste into Notepad
amend
strTempFile = "c:\folder\subfolder\temp.txt"

code cribbed from Copy Data from Excel to Notepad

Sub OpenNotepadWithTempFileWithClipboardContent()
    Dim a As Integer
    Dim rngData As Range
    Dim strData As String
    Dim strTempFile As String

    ' copy some range values
    a = Sheets("Classification").Range("A" & Rows.Count).End(xlUp).Row
    Set rngData = Sheets("Classification").Range("A1:I" & a)
    rngData.Copy

    ' get the clipboard data
    ' magic code is for early binding to MSForms.DataObject
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipBoard
        strData = .GetText
    End With

    ' write to temp file
    strTempFile = "c:\folder\subfolder\temp.txt"
    With CreateObject("Scripting.FileSystemObject")
        ' true to overwrite existing temp file
        .CreateTextFile(strTempFile, True).Write strData
    End With

    ' open notepad with tempfile
    Shell "cmd /c ""notepad.exe """ & strTempFile & """", vbHide
 
    Application.CutCopyMode = True
End Sub

 

[Jaafar Tribak]

I have the following VBA which copies a dynamic range. However, I want to clear the marching ants, but retain the clipboard so I can paste into another application. Can anyone assist?

You may find this of interest:

A very odd clipboard behaviour !

Try this code : Option Explicit #If Win64 Then Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongLong) As Long Declare PtrSafe Function CloseClipboard Lib "user32" () As Long #Else Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long...

www.mrexcel.com

 

[bepedicino]

Thank you Yongle for your assistance. Is there a way to simply retain the selected data on the clipboard after it is copy. I want to manually be able to paste it into a variable email reply.

 

[Yongle]

I do not think I understand your problem
If I run your code (below) I am able to paste into an email and the original clipboard value is retained
Can you explain exactly what you are trying to do
- why are you trying to clear the marching ants?

Sub CopyData()
    Dim a As Integer
    a = Sheets("Classification").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Classification").Range("A1:I" & a).Copy
    ActiveSheet.Range("A2").Select
End Sub

 

[bepedicino]

The issue could be resolved by simply pasting the selection back with formatting. How can I do this?

 

[Yongle]

You are asking for help but do not tell us what you are unable to do

Your code allows you to copy the range
You are then pasting that into an email

What are you trying to do next that is not working?

 

[slack7639]

Clear the marching ants, to reduce chaos (Excel 2010):

Selection.Borders(xlEdgeBottom).LineStyle = xlNone