Copy and paste visible cells in excel

[Murali_MohanKon]

Hi Team,

Please help for Copy and paste visible cells data in excel using code stage or any alternatives. thank you.

------------------------------
Murali Mohan Konanki
UTC
8096961984
------------------------------

[zdenek.kabatek]

Hi, Murali,

you mean visible cells when you use filtering or visible cells which are visible on a particular screen?

If you need the visible cells after filtering this code should work for you
newRange = ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

Then probably you need to select this new range, copy.
newRange.Select
Selection.Copy

Then go to a worksheet where you want to paste it
ActiveSheet.Paste

That should work.

Regards,


------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

[AyushTripathi1]

Hi, 

I tried using this on Copy and Paste Worksheet Range Action in the MS Excel VBO but i get an error saying "xlCellTypeVisible is not declared. It maybe due to its protection level"

Can you help is this because Blueprism code stage will not let me use it?
I am using v 6.4.1 and trying to update the existing MS Excel VBO Action "Copy and Paste Worksheet Range"

------------------------------
Ayush Tripathi
------------------------------

[Janu]

Hi,

I have a excel sheet with 7 spreadsheets, I have to filter for specific criteria and I want to copy the filtered data from one sheet to another. So for copying the visible cells I am trying to use your code, but I am getting  the following error. Can you please help me with this.

Code:
Dim Worksheet As Object
Worksheet = GetWorkbook(handle, Nothing). ActiveSheet
newRange = Worksheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

Error:
Internal : Could not execute code stage because exception thrown by code stage: Unable to get the SpecialCells property of the Range class



Thanks

------------------------------
Aarthi Komandur Seshadri RPA Developer
------------------------------

[krishna.kodimurali9]

Hi Aarthi,

To copy filtered rows to a different worksheet , I use the below custom action.it works for me.


Code :
Dim dstwb, sws, dws as Object
Const xlCellTypeVisible as Integer = 12
Try
dstwb = GetWorkbook(handle, dstworkbook)
sws = GetWorkSheet(handle, srcworkbook, srcworksheet)
dws = GetWorkSheet(handle, dstworkbook, dstworksheet)

dstwb.Application.DisplayAlerts = False
sws.Range(srcrange).Offset(1,0).SpecialCells(xlCellTypeVisible).Copy _
(Destination:=dws.Range(dstrange))
dstwb.Application.DisplayAlerts = True
Success = True

Catch e as Exception

Success = False
Message = e.Message

End Try






------------------------------
Murali Krishna
RPA Consultant
------------------------------

[krishna.kodimurali9]

Hi Murali ,
 You can use below, it works for me.



Code :

Dim dstwb, sws, dws as Object
Const xlCellTypeVisible as Integer = 12
Try
dstwb = GetWorkbook(handle, dstworkbook)
sws = GetWorkSheet(handle, srcworkbook, srcworksheet)
dws = GetWorkSheet(handle, dstworkbook, dstworksheet)

dstwb.Application.DisplayAlerts = False
sws.Range(srcrange).Offset(1,0).SpecialCells(xlCellTypeVisible).Copy _
(Destination:=dws.Range(dstrange))
dstwb.Application.DisplayAlerts = True
Success = True

Catch e as Exception

Success = False
Message = e.Message

End Try

------------------------------
Murali Krishna
RPA Consultant
------------------------------

[Janu]

Hi Murali,

Thanks for your reply. Actually I don't know the exact range of visible cells, I need to know before copying them. I tried using the Get number of Rows action in MS Excel VBO but it gives me the complete rows and not the filtered rows. Can you please help me out how to customize the code to get the Number of Visible rows. Also I want to copy and paste specific column from the filtered worksheet. I have 30 columns but I only need to copy two columns after filter. 

Thanks in advance

------------------------------
Aarthi Komandur Seshadri RPA Developer
------------------------------

[krishna.kodimurali9]

Hi Aarti,

You can provide the complete row count that you get from "Get Number Of Rows" and provide it as a range in the action inputs i have provided. Eg- "A1:C200".

The code works in a way that after you filter, Only the filtered visible rows will be copied to the target.

 Give it a try, it will work.


------------------------------
Murali Krishna
RPA Consultant
------------------------------

[Janu]

Hi Murali

Appreciate your response. Sure I will try with this. Can you tell me what value should I give for xlcelltypevisible. You have given as 12, from where' you got this value. 

Thanks 

------------------------------
Aarthi Komandur Seshadri RPA Developer
------------------------------

[krishna.kodimurali9]

It's a constant, no need to change, Just provide the inputs and run.

------------------------------
Murali Krishna
RPA Consultant
------------------------------