Solved

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

Posted on 2011-09-21
13
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
NEED TO LOOK FOR EXACT NUMBER 14 43
EXCEL String-handling question ... 7 51
Excel not storing numeric string correctly 5 41
Day Count issue - Days360? 12 16
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

732 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