VBA, Copy, Remove Ants, Keep Clipboard

bepedicino

Board Regular
Joined
Sep 29, 2014
Messages
70
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,074
Office Version
365
Platform
Windows
so I can paste into another application

Unrefined example to paste into Word
VBA Code:
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

VBA Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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:
 

bepedicino

Board Regular
Joined
Sep 29, 2014
Messages
70
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,074
Office Version
365
Platform
Windows
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?

VBA Code:
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

Board Regular
Joined
Sep 29, 2014
Messages
70
The issue could be resolved by simply pasting the selection back with formatting. How can I do this?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,074
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,916
Messages
5,471,487
Members
406,766
Latest member
Parasoner

This Week's Hot Topics

Top