Solved

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

Posted on 2011-09-21
13
192 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

932 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

11 Experts available now in Live!

Get 1:1 Help Now