Solved

How to automate a procedure after a copy command so paste is appended in Excel?

Posted on 2011-09-21
13
187 Views
Last Modified: 2012-05-12
I have a situation where some one has to copy about 3000 partial web pages one by one and append them in Excel starting off from a blank worksheet. My database system will after know how to recognize the begining and end of each copy. This will not be an issue.
The problem is that after a copy, the person will need to shfit to the Excel window, position the pointer on the first empty cell  and then paste. Then, move back to the web page and move on to following page to copy from and then repeat the procedure over and over again. This procedure, due to the volume, may cause humam errors such as duplicating or skippimg.
What I would like is a way that when copy is done, that the procedure of appending on the boittom of the worksheet would be done automatically. until a specific command closes down this code.
I would appreciate receiving a full working sample. Feel free to give names to the Excel objects (filename, etc). Consider that just text (no images) will be copied and will always be with a fixed number of columns, but with variable number of rows.

Thanks in advance,
Robert
0
Comment
Question by:Roberth509
  • 5
  • 4
  • 4
13 Comments
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 50 total points
ID: 36576563
Hello Roberth509,
The following code will find the last cell (regardless of what column), go two rows below that cell, and paste whatever is in the clipboard into column 1.  I *think* this is what you're requesting but if not let me know and I'll try again.

Kyle
Sub FindLastRowAndPaste()
Cells(Cells.Find("*", [A1], , xlWhole, xlByRows, xlPrevious).Row + 2, 1).Select
ActiveSheet.Paste
End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:kgerb
ID: 36576574
I forgot to mention.  If you assign this macro to a shortcut key your worker could just copy the stuff from the web page, switch to Excel, and then hit the short cut key.  The stuff will be automatically be pasted to the correct location.  Just a thought.

Kyle
0
 

Author Comment

by:Roberth509
ID: 36576752
Getting close...

I´d prefer he didn´t have to make his way to the Excel window.
If he could copy, then use ashortcut key from there that would take the procedure foward without moving him away from the web page, that would be the ideal solution. All he would nead to worry about would be to move to the next page. If he goes and comes back, chances are that he might lose track where he stands.....
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36576811
hmmm....I'm not sure how this will work.  I'm not a programming expert but I think this may be a sticking point.  Then again, I get in over my head quickly as soon as I leave the Excel VBA realm.  Maybe someone else can chime in who knows more about controlling other programs with VB.  I'm concerned that there will be no easy way to keep Excels shortcuts active after Excel loses focus.  I don't know though, maybe someone else will have a more definitive answer.

Kyle
0
 
LVL 2

Expert Comment

by:jan24
ID: 36577272
Here's something which is very close to what you want.  It's nearly as simple to use and doesn't involve programming outside of Excel VBA.  Instructions are:
1. Press the start button ... this makes it "live"
2. ALT+TAB to Internet Explorer (or whatever)
3. Select and CTRL+C to copy what you want
4. ALT+TAB to Excel and watch it paste in the data
5. Repeat from step 2. as often as you like
6. Press the stop button when you're finished.

Let me know if this does the trick. Book5.xlsm
0
 

Author Comment

by:Roberth509
ID: 36588272
jan24,

Your solution is almost close to the best one could imagine and I´ll be extremely happy to reward you if you could perhaps just do one more thing.

Sometimes one wants to simply look and check at the last page copied on the spreadsheet. Barely for checking purposes. But then another paste is done. Time is then lost to erase it.

Could you please creat a third button called SPECIAL PASTE that could be used if your frst solution is not triggered. In other words, We´d have 2 alternative solutions: one as it is now, and the other would require the user to press this new button everytime he'd want to paste after the last empty cell in column A.

Thanks a lot
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Roberth509
ID: 36588406
kgerb and jan24,

I´m sorry, but what I just asked for is basically the solution provided by kgerb.
So, to be fair with him, I will grant him also with a small share....

But I would appreciate having this solution added to jan24s one...as a third button which is installed together wiyth the other two.

Thank you,
Robert
0
 
LVL 2

Expert Comment

by:jan24
ID: 36588414
Sure - no problem.  I'm on a train right now but I'll crack on with it this evening.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36588570
Roberth509,
Please, by all means, give it to jan24.  His solution was much superior to mine.  He deserves the points and the grade.  I was just happy to see someone who has a clue help you.  I'm just a hack engineer who knows a little VBA.  The solution is his.

Kyle
0
 
LVL 2

Accepted Solution

by:
jan24 earned 450 total points
ID: 36590188
Here it is with a Special Paste button.  You can also use CTRL+SHIFT+V as a shortcut for Special Paste.  Let me know if this is what you wanted.

I'm relaxed on the points.  Roberth509 was definitely on the right track and the important thing is getting to a solution that works!
Book5--v2-.xlsm
0
 

Author Closing Comment

by:Roberth509
ID: 36712188
I am very grateful...great guys...
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36712214
You're welcome:)
0
 
LVL 2

Expert Comment

by:jan24
ID: 36713261
Ditto!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now