Copy & Paste Multiple Cells in Excel 2008

Scripting Forums AppleScript | Mac OS X
#1

Just made the switch to the mac from a PC and am trying to take advantage of AS for some things that I could do rather easily with AutoHotKey on the PC, but I haven’t been able to figure this one out.

Here’s my pseudo-code…

tell Excel
get the active cell
copy the active cell and the cell to the right of it
go down one row
paste the copied cells
go down one row
paste the copied cells
go down one row
end tell

And added bonus would be the ability to tell the script how many times to loop through this action.

Thanks in advance for the help!
Ryan

#2

You could use this

tell application "Microsoft Excel"
	set countOfCopies to 3
	
	set sourceRange to get resize active cell column size 2
	set destinationRange to get resize (get offset sourceRange row offset 1) row size countOfCopies
	
	set value of destinationRange to get value of sourceRange
end tell

more compact, with a slightly different result depending on whether any formulas are involved is

tell application "Microsoft Excel"
	set countOfCopies to 3
	
	set sourceRange to get resize active cell column size 2
	
	fill down (get resize sourceRange row size countOfCopies)
end tell

You might want to look at
http://developer.apple.com/mac/library/documentation/AppleScript/Conceptual/AppleScriptLangGuide/introduction/ASLR_intro.html
which will tell you how to use the command Display Dialog to input a number

You might also want to download Microsoft’s Apple Script developers guide
http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=4acff5ca-5863-4bb6-9a3b-09d2bc0d1dc71033&srcid=e1dbbe49-e45b-4606-bc00-dc5d3bd2d4601033&ep=7

#3

Thanks so much! This works like a charm. How can I move the active cell down three rows after the fill down command?


tell application "Microsoft Excel"
	set countOfCopies to 3
	
	set sourceRange to get resize active cell column size 2
	
	fill down (get resize sourceRange row size countOfCopies)
end tell

Thanks also for the links. As I mentioned, I’m just getting started and appreciate the resources.

Ryan

#4

There ususaly isn’t a need to Select or to use Active Cell

tell application "Microsoft Excel"
    Select (get offset Active cell row offset 3)
End tell
#5

Perhaps

tell application "Microsoft Excel"
	
	set countOfCopies to 3
	
	set startRange to range (get address active cell)
	
	set lastRow to first row index of (get end (cell 1 of (row -1 of (cells of (get entire column of startRange)))) direction toward the top)
	
	set fillrange to get resize startRange column size 2 row size countOfCopies
	
	repeat while (first row index of cell 1 of row 1 of fillrange) ≤ (lastRow)
		
		fill down fillrange
		
		set fillrange to get offset fillrange row offset countOfCopies
	end repeat
end tell
#6

Thanks so much! This is great.

I’m a medical student and take my board exams on July 2nd. After that I get a month of vacation and will definitely be spending some of that time learning more AS. I’m glad I found this community.

I appreciate the help.

Ryan