Select and copy filtered cell in excel - (Moved)

[AshishS]

Hi friends,

I am new to Autoit and would like to automate excel task. I am trying to bring source formatted vlookup text to another sheet. I am looking for autoit to help me but I got stuck in autoit code. My idea is to select the filtered excel cell value with this code, copy it and paste at destination cell but I don't know how to select specific filtered cell so that I can copy it. Need your help to get rid of this.

$oRange = $oExcel.ActiveSheet.Range("A1:AK871")
For $oRow In $oRange.Rows
    If $oRow.Hidden = False Then
        ConsoleWrite($oRow.Columns(37).Value)
        Send("^c")
    EndIf
Next

Also let me know if there is a better way to do this task in excel only.

[water]

Welcome to AutoIt and the forum!

How to retrieve all filtered rows can be found in the wiki.

[AshishS]

Water, Thanks for reply.

I don't need to store my data in array or variable, as it will not copy the source formatting.

I just need to select the filtered specific cell and copy that output in excel and paste at destination.

[Nine]

It is much more simple and efficient to store it in an array and write it to the target excel destination.  Copy / paste is for human.  Arrays are for machine.  Take the suggestion of @water and look at _Excel_RangeWrite.  You will get script in no time...

[water]

As $oArea is a Range object you could use function _Excel_CopyPaste to copy each Area of the filtered sheet.

[JLogan3o13]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[AshishS]

Thanks all for your reply.

Hey @water,

I implemented _ExcelRangeCopyPaste and it works great for me. Thanks a ton!

Only problem is that it will copy the header cell as well, which is not required. Please help me to find where I am wrong.

Thanks in advance.

For $oRow In $oExcel.Worksheets("1").AutoFilter.Range.Rows
    If $oRow.Hidden = False Then
        _Excel_RangeCopyPaste($oExcel.ActiveSheet, $oRow(1).Columns(3), $oExcel.Worksheets("2").Range($Fi))
    EndIf
 Next

 

[water]

You could simply delete the header range after you have copied all filtered  ranges.