VBA: Paste from Clipboard to Active Cell (then TAB key)

[zero269]

Hello,

I'm having trouble pasting from the Windows Clipboard into the Active Cell in Excel.

I've tried a few variations online but none of them work.

The one that looks like the simplest is:

Sub PasteValues()
ActiveSheet.PasteSpecial Paste:=xlPasteValues
End Sub

However, this throws a run-time error '1004'

NOTE: I'm copying a value from a web page and trying to just Paste it into the active cell.
I'd like to Tab (key) which should move to the next row in my case within the Table.

Microsoft's information on Worksheet.Paste method is not very helpful. Their EXAMPLE:

Worksheets("Sheet1").Range("C1:C5").Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5")

I'm not using their COPY line because I'm trying to PASTE from the Clipboard, and they indicate the Destination is optional and that it defaults to the active selection.

Thank you...

 

[jolivanes]

How do you get it into the clipboard?

 

[zero269]

How do you get it into the clipboard?

Hi Jolivanes,

I'm copying it from a web page. Right now it's just a number; usually up to 6 digits long.

It's a bit cumbersome clicking into the cell > Paste > Tab
Rinse and repeat...

It's going into a two column Table that generates a URL based on that number I'm pasting in. I hid Column B so I didn't have to hit Tab twice to move to the next row (which extends the table).

I figure if I can create a Macro > assign it to a Button (Shape), I could just COPY > Click and life would be so much easier...

Maybe later on I'll even expand on it using some sort of validation criteria, but I'm taking baby steps since I'm new to VBA in Excel.

 

[jolivanes]

You'll need to select a cell where you want the data pasted whatever you do but you can use keyboard shortcuts.
There are several different ones you can use. I use "Ctrl+Insert" and "Shift+Insert" mostly for copying and pasting but there are others as well.

 

[zero269]

you can use keyboard shortcuts

Thanks jolivanes,

I'm a big fan of keyboard shortcuts myself, but unfortunately, I need a VBA solution so I can ensure the Table expands to the next row automatically so I can paste the next value quickly. I usually add around 100 values at any given time and that's why pressing keys is a bit painful. Double Click > CTRL+C > Click (Macro button) should ease the process.

 

[zero269]

I found that the following VBA code will paste what I have copied to the Clipboard. However, it works intermittently.

ActiveSheet.PasteSpecial xlValues

I haven't been able to pinpoint the cause. I often click the cell I want to Paste to - even though the cell is already selected - before clicking my Macro button (Paste Quiz).

 

[SDPanda]

Hi Jolivanes,

I'm copying it from a web page. Right now it's just a number; usually up to 6 digits long.

It's a bit cumbersome clicking into the cell > Paste > Tab
Rinse and repeat...

It's going into a two column Table that generates a URL based on that number I'm pasting in. I hid Column B so I didn't have to hit Tab twice to move to the next row (which extends the table).

I figure if I can create a Macro > assign it to a Button (Shape), I could just COPY > Click and life would be so much easier...

Maybe later on I'll even expand on it using some sort of validation criteria, but I'm taking baby steps since I'm new to VBA in Excel.

Sounds like we are trying to do the same thing.

I found Method 1 to get me closer to what I wanted but all the data pastes into a single cell currently:

How to Paste From Clipboard to Excel Using VBA - ExcelDemy

VBA allows to paste from the clipboard with some codes. In this article, you'll get 3 methods of using VBA to paste from clipboard to Excel.

www.exceldemy.com

 

[zero269]

I found Method 1 to get me closer to what I wanted but all the data pastes into a single cell currently:

I saw that one too, but it throws a compile error for me. I also don't like that it references a specific cell to paste to... as if anyone is pasting data into one cell over and over again...
I've been focusing on the ActiveCell option, but only had success with the ActiveSheet version.
It's still giving me problems...

 

[SDPanda]

I saw that one too, but it throws a compile error for me. I also don't like that it references a specific cell to paste to... as if anyone is pasting data into one cell over and over again...
I've been focusing on the ActiveCell option, but only had success with the ActiveSheet version.
It's still giving me problems...

Haha, well this is a "feeder" sheet for me. I am trying to copy data from a table on a website to ActiveCell A2 where it will create multiple worksheets based on values in column H and copy/paste the relevant rows to each worksheet. There's always more than one way to do something so hopefully somebody comes along to enlighten us

 

[zero269]

It looks like I was finally able to achieve the results I was looking for:

Paste values only from the Clipboard to the active cell (or ActiveSheet) as it appears might be the correct term.

Sub testPaste()
'
' Paste values as text only to active cell (ActiveSheet)
'
    ActiveSheet.Activate
    ActiveSheet.PasteSpecial Format:="Text"

End Sub

Before:

After a few uses:

I tested it outside my Table as well to confirm it's pasting in the Active Cell.

Note: I did not test a range... as I'm not pasting a range of values.

It turns out I needed to "Activate the worksheet first in order to use the properties of the worksheet" (Step 4 here). Although I've tried that in previous examples, specifying the Sheet Name would throw an error I couldn't figure out.

ActiveSheet.Activate will activate whatever Sheet I'm currently in making this a more dynamic solution considering I can select any Worksheet and Cell to Paste into within my Workbook.

I saved this in my Personal Workbook so I can use it for any open workbooks.

Create and save all your macros in a single workbook - Microsoft Support

Create and save all your macros in a single workbook by creating them in Personal.xlsb, a hidden workbook that opens every time you open Excel. Macros created in Personal.xlsb are available every time you open a workbook in Excel.

support.microsoft.com

Copy your macros to a Personal Macro Workbook - Microsoft Support

To reuse your macros, you need to copy them to a special workbook on your computer called a Personal Macro Workbook (personal.xlsb).

support.microsoft.com