Solved

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

Posted on 2014-12-02
13
257 Views
Last Modified: 2014-12-03
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.
0
Comment
Question by:kbay808
  • 7
  • 5
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40477999
You need VBA to paste into cell A4?

Cells(4, 1).Select
ActiveSheet.Paste

Open in new window

0
 

Author Comment

by:kbay808
ID: 40478017
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478040
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:kbay808
ID: 40478061
It works fine in a regular cell, but not in a group of cells that are merged together.
0
 

Author Comment

by:kbay808
ID: 40478084
Cells A3:H41 are merged into 1.  I need it to copy into that merged cell
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478096
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
 

Author Comment

by:kbay808
ID: 40478107
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478112
Is Cell A2 unused, or is it part of another merge?

It runs currently on the attached spreadsheet.
EE.xlsm
0
 

Author Comment

by:kbay808
ID: 40478129
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478136
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40478183
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
 

Author Comment

by:kbay808
ID: 40478187
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
 

Author Closing Comment

by:kbay808
ID: 40478194
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel formatting with conditional formatting 2 42
Automatically report prepared in excel by VBA 5 34
MS Excel Multi Sheet Formula 13 32
Excel VBA 30 38
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question