Applescript Microsoft Excel insert copied range from another worksheet

Question

I want to copy rows 30 to 40 from worksheet 4 and insert them before row 30 in worksheet 3 of the same workbook. Please suggest if you could create a dialog to select a cell in applescript

tell application "Microsoft Excel"
    set a to entry index of active sheet of active workbook -- worksheet 3 which is open
    
    activate object worksheet (a + 1) -- worksheet 4
    tell active sheet
        -- Create Source Range
        set rowRange to "30:40"
        set cb to my CalcColumnLetter(count of columns of used range)
        set colRange to "A:" & cb as text
        set srcRange to intersect range1 row rowRange range2 column colRange
        copy range srcRange
    end tell
    activate object worksheet a -- worksheet 3
    set firstCell to "A30"
    -- need to insert srcRange before A30, with shift down. Suggestions plz
end tell

Answer 1

There are simpler ways to construct your ranges. I've included an input box, which will allow you to enter a cell address. I used this to specify the destination cell. If that wasn't your intent, use the commented line instead. The last two activate object are optional and are only there to let you immediately see the deposited data in its new location.

Update: Modify to insert range rather than overwrite

tell application "Microsoft Excel"
    set ci to (input box prompt "Enter cell" type string) -- A20
    with timeout of 3 seconds
        set a to entry index of active sheet of active workbook -- assumes sheet 3
        set s3 to worksheet a -- destination
        set s4 to worksheet (a + 1) -- source
        
        set rowL to "30:40" -- rows to copy
        set rowHei to ((word 2 of rowL as integer) - (word 1 of rowL) as integer) + 1
        --> 11
        
        activate object s4 -- source sheet
        set sur to used range of active sheet
        set colWid to first column index of (special cells sur type cell type last cell)
        set iSect to intersect range1 sur range2 row rowL -- source range
        -- select iSect
        
        activate object s3 -- destination sheet
        activate object range ci
        -- set destination range
        set newDest to get resize range ci row size rowHei column size colWid
        select newDest -- optional
        insert into range newDest shift shift down
        copy range iSect destination newDest
        
    end timeout
end tell

NB I used special cells to get the number of columns to use but your method of counting columns would also work. Some of my comments above are no longer applicable. If this solves your problem I'll try and edit them as well.

This uses the insert into range command which can shift cells down or to the right.