CODEWITHSUNDEEP
excelvbaclipboardpaste
Koda
Koda

Reputation: 177

Pasting Values from Clipboard into the first empty row of open Excel spreadsheet

I have a dataframe from R that I need to paste into the first empty row of an open Excel spreadsheet.

I have tried numerous things.

This code throws a "Run-time error '1004': Application-defined or object-defined error". 

Dim NextRow As Range
Set NextRow = Range("B" & Sheets("TC-9").UsedRange.Rows.Count + 1)
Worksheets("TC-9").Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I also tried using xlUp to look from the bottom up to find the first empty row.

Cells(Range("C1000000000").End(xlUp).Row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Upvotes: 0

Views: 7088

Answers (4)

Amit Panasara
Amit Panasara

Reputation: 680

Appending into last of document

Set Range2 = ActiveDocument.Content 
 Range2.Collapse Direction:=wdCollapseEnd 
 Range2.Paste

Reference Link Microsoft-Docs

Upvotes: 0

Ash Notts
Ash Notts

Reputation: 61

Try this

dim lastrow as integer

With Worksheets("TC-9")
    lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    .Range("A" & lastrow + 1).PasteSpecial xlPasteValues, _ 
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End With

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

In your PasteSpecial line, you are specifying a Range as Range("A" & NextRow). NextRow has been defined as a Range itself, and concatenating a String with a Range isn't valid.

Change your code as follows:

Dim NextRow As Long
NextRow = Sheets("TC-9").Range("B" & Sheets("TC-9").Rows.Count).End(xlUp).Row + 1
Worksheets("TC-9").Range("A" & NextRow).PasteSpecial

Note: This will copy a single value from the clipboard. If you want multiple values copied to a single row, it will work if they are tab-delimited but not if they are comma-delimited. (It can probably be done, but would require a bit more work.)

In your second piece of code you used Range("C1000000000") but Excel currently has a limitation of 1,048,576 rows.

Upvotes: 0

Werrf
Werrf

Reputation: 1148

If I'm understanding, you're trying to paste a value in that's in the Windows clipboard. The problem is that you can't use Paste Values when pasting from Windows, because it's not copying an Excel object.

Rather than using Range.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, try just using PasteSpecial on its own:

Range.PasteSpecial

You could also just use the .Paste method, but you'll need to select your range first:

Range("A1").select
ActiveSheet.Paste

It doesn't matter what method you use to find the Range, just how you paste it. I prefer the former method, but both should work.

Upvotes: 0

Related Questions