Blue Prism Product

last person joined: 27 minutes ago 

This community covers the core Blue Prism product.

Expand all | Collapse all

Copy and paste visible cells in excel

  • 1.  Copy and paste visible cells in excel

    Posted 03-09-2021 03:29
    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
    ------------------------------


  • 2.  RE: Copy and paste visible cells in excel

    Posted 03-09-2021 13:38
    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
    ------------------------------



  • 3.  RE: Copy and paste visible cells in excel

    Posted 06-12-2021 16:29
    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
    ------------------------------



  • 4.  RE: Copy and paste visible cells in excel

    Posted 07-26-2021 03:40
    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
    ------------------------------



  • 5.  RE: Copy and paste visible cells in excel

    Posted 07-26-2021 06:00
    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
    ------------------------------



  • 6.  RE: Copy and paste visible cells in excel

    Posted 07-26-2021 06:03
    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
    ------------------------------



  • 7.  RE: Copy and paste visible cells in excel

    Posted 07-26-2021 16:32
    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
    ------------------------------



  • 8.  RE: Copy and paste visible cells in excel

    Posted 07-27-2021 06:18
    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
    ------------------------------



  • 9.  RE: Copy and paste visible cells in excel

    Posted 07-27-2021 09:23

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



  • 10.  RE: Copy and paste visible cells in excel

    Posted 07-27-2021 09:47
    It's a constant, no need to change, Just provide the inputs and run.

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



  • 11.  RE: Copy and paste visible cells in excel

    Posted 07-27-2021 19:38
    Hi Murali
    Thank you So much
    Your Code works!

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



  • 12.  RE: Copy and paste visible cells in excel

    Posted 07-27-2021 20:44
    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
    ------------------------------



  • 13.  RE: Copy and paste visible cells in excel

    Posted 07-31-2021 15:50
    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


  • 14.  RE: Copy and paste visible cells in excel

    Posted 07-31-2021 18:15

    Hi @jackson liam,

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



  • 15.  RE: Copy and paste visible cells in excel

    Posted 07-31-2021 18:23

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



  • 16.  RE: Copy and paste visible cells in excel

    Posted 07-31-2021 20:11
    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
    ------------------------------



  • 17.  RE: Copy and paste visible cells in excel

    Posted 08-02-2021 11:14
    Hi Jackson,

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

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



Welcome to the Blue Prism Product Community!

Whether you’re looking to manage a complex infrastructure, maintain security and compliance, bring new products to market faster, or gain operational speed and agility in an uncertain economy, Blue Prism delivers — with the flexibility you need to create the business you want. From deployment on-premise, through a cloud service provider or as SaaS, to a skillful and adaptable digital workforce that continually expands to meet your enterprise needs, you can gain enhanced operational insight and control while your people reclaim the time they need to focus on great work.

Product PageKnowledge BaseBlue Prism Training Offering

FAQs

Blue Prism is intelligent automation — business-developed, no-code automation that pushes the boundaries of robotic process automation (RPA) to deliver value across any business process in a connected enterprise.

A combination of RPA with expanded cognitive and AI capabilities, Blue Prism is different than other automation technology on the market. With one Blue Prism license, you gain instant access to an already AI equipped digital workforce, along with the tools you need to build and delegate automations. Click here for more information on Blue Prism and Intelligent Automation.
To learn more about how Blue Prism can help your organization and how much it will cost to get started, please Contact our Sales department.
Blue Prism can be downloaded from our customer portal. If you would like to consume or download any material it is necessary to create an account on the Portal. Once you have registered, you can access the download options for Blue Prism here.
Yes! Installed on your own machine and supported by our training materials and product documentation, you can use all the features of the full enterprise product for free with our Blue Prism Trial – giving you the opportunity to learn the basics before moving to a full production implementation. Click here for more information and to download the trial.
Yes! You can access our known issue list for Blue Prism from our Support Portal.
Regardless of your industry, Blue Prism’s Digital Workforce can adhere to strict governance and compliance standards without limiting productivity. Click here for more information on how your industry can benefit from Blue Prism.