Using Cell Number Reference

Scripting Forums AppleScript | Mac OS X
#1

I am trying to copy a range in excel using Cell & Row numbers. Normally I know the starting point but as the worksheet is dynamic do not know last cell in used range.

The script below works but , if there were a lot of columns the list would get very long, is there a way of using column and row numbers?


tell application "applications:Microsoft office 2011:Microsoft Excel.app"
	set ColL to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L"}
	set Colcnt to (((count of columns of used range of active sheet)))
	set ColL to item Colcnt of ColL
	set Rowcnt to (count of rows of used range of active sheet)
	set ColL to "A2:" & ColL
	copy range range ("A2:" & ColL & "" & Rowcnt)
	--To test
	paste special (range "K2") what paste values
end tell

Thanks

Peter

#2

Hope this helps!


tell application "Microsoft Excel"
	
	tell active workbook
		
		tell active sheet
			
			-- this will get the entire used range minus the first row
			-- e.g. $A$2:$J:$20
			set sourceRange to intersect range1 (used range) range2 (get offset of (used range) row offset 1)
			
			-- this sets the destination to the second row of the column immediately after the current used range
			-- e.g. $K$2
			set destRange to get offset of (get end of sourceRange direction toward the right) column offset 1
			
			-- then do your copy and paste in 1 of 3 ways...
			
			-- 1. if you only want the values...
			--  a. if you want to use the clipboard
			copy range sourceRange
			paste special destRange what paste values
			
			--  b. if you want to bypass the clipboard
			-- first resize to match the sourceRange , e.g. $K$2:$T$20
			--set destRange to get resize destRange row size (count of rows of sourceRange) column size (count of columns of sourceRange)
			--set value of destRange to get (value of sourceRange)
			
			-- 2. if you also want the formatting...
			--copy range sourceRange destination destRange
			
		end tell
		
	end tell
	
end tell
#3

Thanks very much I think that is going to work and a lot more elegant than my attempt.:):slight_smile: