topic RE: Copy and paste visible cells in excel in Product Forum

Copy and paste visible cells in excel

Murali_MohanKon — Tue, 09 Mar 2021 03:29:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

zdenek.kabatek — Tue, 09 Mar 2021 13:38:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

AyushTripathi1 — Sat, 12 Jun 2021 15:29:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

Janu — Mon, 26 Jul 2021 02:40:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

krishna.kodimurali9 — Mon, 26 Jul 2021 05:00:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

krishna.kodimurali9 — Mon, 26 Jul 2021 05:03:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

Janu — Mon, 26 Jul 2021 15:32:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

krishna.kodimurali9 — Tue, 27 Jul 2021 05:18:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

Janu — Tue, 27 Jul 2021 08:23:00 GMT

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
------------------------------

RE: Copy and paste visible cells in excel

krishna.kodimurali9 — Tue, 27 Jul 2021 08:47:00 GMT

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

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

RE: Copy and paste visible cells in excel

Janu — Tue, 27 Jul 2021 18:38:00 GMT

Hi Murali
Thank you So much
Your Code works!

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

RE: Copy and paste visible cells in excel

Janu — Tue, 27 Jul 2021 19:44:00 GMT

Hi Murali.
Thank you again for your help. This code works fine, just that it adds one extra row at the end, could you please tell me if there is a way to not have that extra row created

You can see the from the above screenshot, next to TEST2 row, one additional row is added.

Thanks

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

RE: Copy and paste visible cells in excel

jacksonliam — Sat, 31 Jul 2021 14:50:00 GMT

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.

o 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

------------------------------
Gordon Bullard & Company

RE: Copy and paste visible cells in excel

ritansh.jatwani — Sat, 31 Jul 2021 17:15:00 GMT

Hi @jacksonliam,

In order to apply filter for specific criteria, you can refer below video:

https://www.youtube.com/watch?v=GiQgvUhbFM0

Post applying filter, since you want to copy paste the data to other sheet, please follow below steps:

1) After applying filter, Use "Select" action of "MS Excel VBO" to select the data (e.g. A:D). This will select the whole column A to D (assuming the filter is applied on column A to D).
2) Use "Copy" action of MS Excel VBO to copy the selected data.
3) Use "Activate Worksheet" of MS Excel VBO to activate the destination worksheet.
4) Use "Go to Cell" action of MS Excel VBO and go to the cell where it needs to be pasted (e.g. A1)
5) Use "Paste" action of MS Excel VBO to paste the data. It will copy and paste the filtered set of data.

Hope it helps.

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Copy and paste visible cells in excel

Janu — Sat, 31 Jul 2021 17:23:00 GMT

Hi

you can use the above code which was mentioned by Murali. I watched the w video on YouTube by Ritansh jatwani. After auto-filtering I used the code shared by Murali and it's absolutely working fine.

For getting the range: you can use get number of rows action available in Ms Excel VBO and just use this inside the code.

thanks Ritansh for making a video on Auto filter. Your code exactly worked for me.

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

RE: Copy and paste visible cells in excel

ritansh.jatwani — Sat, 31 Jul 2021 19:11:00 GMT

Hi @Janu,

Thanks for the feedback. Glad to know that the video helped you. :)

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Copy and paste visible cells in excel

krishna.kodimurali9 — Mon, 02 Aug 2021 10:14:00 GMT

Hi Jackson,

Can you share a snap of the excel sheet you are working on?

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