• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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

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
Roberth509
Asked:
Roberth509
  • 5
  • 4
  • 4
2 Solutions
 
kgerbChief EngineerCommented:
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
 
kgerbChief EngineerCommented:
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
 
Roberth509Author Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
kgerbChief EngineerCommented:
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
 
jan24Commented:
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
 
Roberth509Author Commented:
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
 
Roberth509Author Commented:
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
 
jan24Commented:
Sure - no problem.  I'm on a train right now but I'll crack on with it this evening.
0
 
kgerbChief EngineerCommented:
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
 
jan24Commented:
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
 
Roberth509Author Commented:
I am very grateful...great guys...
0
 
kgerbChief EngineerCommented:
You're welcome:)
0
 
jan24Commented:
Ditto!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now