How to create a vba code that will paste a data from the clipboard copied from IE to cell A4?

Cell A4 is really cells A3:H41 merged into 1.

After I copy the data from IE, I double click on cell A4 and then paste.  All of the data is populated in just that cell.  I need a vba code that will do the same thing.
kbay808Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You need VBA to paste into cell A4?

Cells(4, 1).Select
ActiveSheet.Paste

Open in new window

0
kbay808Author Commented:
Sorry, I need to paste into cell A3

When using this code, there is an error for "ActiveSheet.Paste"

Sub Paste_Journal()
    Cells(3, 1).Select
    ActiveSheet.Paste
End Sub

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It works fine on my computer - do you have something in the clipboard that you can paste into Excel? Do you have protection in your spreadsheet? Does actually pasting in that cell work?

Maybe try this small modification:

    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.Paste

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

kbay808Author Commented:
It works fine in a regular cell, but not in a group of cells that are merged together.
0
kbay808Author Commented:
Cells A3:H41 are merged into 1.  I need it to copy into that merged cell
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then I'm going to cheat.

I'm going to assume that cell A2 is currently unused - change it to whatever you want.

ActiveSheet.Cells(2, 1).Select
ActiveSheet.Paste
ActiveSheet.Cells(3, 1).Value = ActiveSheet.Cells(2, 1).Value
ActiveSheet.Cells(2, 1).Value = ""

Open in new window

0
kbay808Author Commented:
I'm getting the following error: "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?".  When I click on "OK", I get a run-time error.  When I debug, "ActiveSheet.Paste" is highlighted.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Is Cell A2 unused, or is it part of another merge?

It runs currently on the attached spreadsheet.
EE.xlsm
0
kbay808Author Commented:
Cell A2 is not free, but cell A101 is.  I tried your spreadsheet.  I did not get the wrong size error, but I did get the Run-time error.  The data that I'm coping is equivalent to coping your code from your above post.  It's in a similar box like the code is in.  Try coping that and you should be able to duplicate the error.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here is an updated spreadsheet.

So I copied the text that you can see, and pressed the button, and it works.

If it doesn't work for you, please let me know what text you are trying to copy.
EE2.xlsm
0
Rory ArchibaldCommented:
Try this:
Sub Macro2()
    On Error Resume Next
   ' this is a late bound MSForms.DataObject
   With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
      .GetFromClipboard
      Range("A3").Value = .GetText
   End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
I misunderstood you when you stated that you were going to cheat.  I thought when you ask for a cell that was free that it was just going to be used as a temporary place holder or a blank value or something.
 
When I copied your code from the Experts Exchange website and clicked on the button, the first line was in cell A3.  Lines 2 through 4 were in cells A102:A104.  What I need is for all of the lines to be populated in cell A3.

It may help to know what I’m doing with the data.  What I’m doing is pasting an entire work log into cell A3.  Then I’m using a macro to highlight key words in the work log.
0
kbay808Author Commented:
I did not refresh before you made your last post so my last post was not needed.  Your code work perfect.  Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.