This however, has me stumped. My problem is I have 2 spreadsheets where there isn't a unique key in each order to do a formula. There is a name in both spreadsheets, however, one is in three columns and the other is in one. And to top that off, they aren't the same exactly. One contains some middle names and the other middle initials. So I am going to have to visually look down the column in one spreadsheet and glance at the other spreadsheet for a match. Once I see a match, I make sure my focus is on the cell I want to paste the data to in the destination spreadsheet and then click on the cell I want to copy from the source spreadsheet. At that point, I simply want to copy the data in the active cell, switch to the other spreadsheet (Send, {AltTab}), paste the data into that cell and return to the original spreadsheet.
I've done a lot of searching in the posts and have tried numerous methods. My code gets messy as I comment things out and change the code. Below is most of my code with comments. I have one destination spreadsheet and several source spreadsheets to go through. Any help to speed up the copy/paste function would be helpful. Thank you in advance!
Dianne
Code: Select all
#SingleInstance force
^z::
file2open :="path of my source file" ; destination file is already open
XL := ComObjCreate( "Excel.Application" )
Clipboard:= ;to clear the clipboard
clipwait ; to wait for it to empty
Sendinput, ^C ; copy the email address in the active cell
clipwait
Send, {AltTab} ; move to the destination spreadsheet
ComObjActive("Excel.Application").ActiveCell.value := clipboard ; email address to excel active cell
Sendkey, {right} ;this is to move one cell to the right to accept the next step
Send, {AltTab} ; move back to the source spreadsheet
Sendkey {right} ; move to the next cell in the source spreadsheet to get the phone number
Clipboard:= ;to clear the clipboard
clipwait ; to wait for it to empty
Sendinput, ^C ; copy the email address in the active cell
clipwait
Send, {AltTab} ; move to the destination spreadsheet
ComObjActive("Excel.Application").ActiveCell.value := clipboard ; phone number to excel active cell
Send, {AltTab} ; move back to the source spreadsheet
; Visually scan down the column looking for the next match. I know this is ugly but using the script would speed up copying/pasting back and forth
When I find another match, click ^z to run it again
I've also tried using different code to do the copy/paste:
Send {lcontrol down}c{lcontrol up}
Send {lalt down}{tab}{lalt up}
Send {lcontrol down}v{lcontrol up}
etc.