none
VBA - Copy Visible Column Cells to another Worksheet - Array RRS feed

  • Question


  • hi friends,

    I wanted to only copy the visible cells from  worksheet  1  to Worksheet 2  -   using an array,

    I am stuck on the special visible cells

    I have included the pseudo code below, as im not sure the correct approach to use



    Sub Copy_Paste_Special_Cells() 'pseudocode Dim oSourceWksht As Worksheet Dim oDestinationWksht As Worksheet Dim i As Long Set oSourceWksht = ThisWorkbook.Worksheets("Sheet1") Set oDestinationWksht = ThisWorkbook.Worksheets("Sheet2") 'from stack exchange '---------------- 'Special visible cells

    Set r = r.SpecialCells(xlCellTypeVisible) For Each rC In r j = j + 1 If j = 10 Or j = r.Count Then Exit For Next rC Range(r(1), rC).SpecialCells(xlCellTypeVisible).Copy '-------------- oCopyRange = Array("Column A - Visible Cells", "Column B Visible Cells") oDestinationRange = Array("A1", "A50") For i = LBound(oCopyRange) To UBound(oCopyRange) oSourceWksht.Range(oCopyRange(i)).Copy Destination:=oDestinationWksht.Range(oDestinationRange(i)) Next End Sub




    thank you for your help


    Cheers Dan :)


    • Edited by Dan_CS Tuesday, November 14, 2017 1:12 PM
    Tuesday, November 14, 2017 10:43 AM

All replies

  • Hi,

    I suppose it would be good to provide pseudo code to others. 
    But your code seems to be a mixture of recording macro and copied code from somewhere.

    Didn't you see some errors when you executed your code?
    Make sure that you don't forget something before providing your code.

    Regards,

    Ashidacchi

    Tuesday, November 14, 2017 12:16 PM
  • Hello Ash,

    yes i have some working code, but i am trying to incorporate the special cells that are visible.

    I am not sure how to isolate the column ranges that are visible :(


    Cheers Dan :)

    Tuesday, November 14, 2017 1:12 PM
  • Luckily i was able to work it out   :)

        
        Sub Copy_Paste_Special_Cells()
        
    
        Dim oSourceWksht                As Worksheet
        Dim oDestinationWksht           As Worksheet
        Dim i                           As Long
        
      
        
    
        Set oSourceWksht = ThisWorkbook.Worksheets("Sheet1")
       
        Set oDestinationWksht = ThisWorkbook.Worksheets("Sheet2")
        
       
    
        oCopyRange = Array("A2:A100", "B2:B100")
    
        oDestinationRange = Array("A1", "A50")
       
        
        For i = LBound(oCopyRange) To UBound(oCopyRange)
    
        oSourceWksht.Range(oCopyRange(i)).SpecialCells(xlCellTypeVisible).Copy Destination:=oDestinationWksht.Range(oDestinationRange(i))
        
        Next
        
        End Sub
    

    It was a lot simpler than what i had tested before

     
    oSourceWksht.Range(oCopyRange(i)).SpecialCells(xlCellTypeVisible).Copy

     did the trick

     

     


    Cheers Dan :)

    Tuesday, November 14, 2017 3:52 PM
  • Hi Dan,

    I'm glad to hear that your original issue has been resolved. I would suggest you mark your solution as answer to close this thread. If you have any other office development issue, please feel free to post thread to let us know.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 15, 2017 5:03 AM