locked
Copy and paste visible cells in another sheet RRS feed

  • Question

  • Hi,

    I have a macro which hides few rows and columns. After that i need to copy the only visible cells and paste it in another worksheet.

    This code throws me an error, can some one help me.

    Dim Rang as range

    Set Rang = Selection.SpecialCells(xlCellTypeVisible)

    rang.copy

    sheets("sheet2").Range("B2").paste

    Wednesday, July 25, 2012 3:35 PM

Answers

  • Robo,

    Try this,

    Dim rang As Range
    
    Set rang = Range("A1:Z" & _
        Cells.SpecialCells(xlCellTypeLastCell).Row).SpecialCells(xlCellTypeVisible)
    
    rang.Copy Sheets("Sheet2").Range("B2")

    • Proposed as answer by Mike7952 Thursday, July 26, 2012 9:14 PM
    • Unproposed as answer by Mike7952 Thursday, July 26, 2012 9:14 PM
    • Proposed as answer by Leo_Gao Monday, July 30, 2012 1:37 AM
    • Marked as answer by Leo_Gao Thursday, August 2, 2012 1:07 AM
    Wednesday, July 25, 2012 7:01 PM

All replies

  • Have you selected the entire range before running this code?

    What is the error message and on which line does it occur?


    Regards, Hans Vogelaar

    Wednesday, July 25, 2012 3:54 PM
  • no i have not selected the range. Criteria here is.. in my macro data gets filtered out and i need to work on the filtered data. So rather spoiling the current filtered data sheet wanted to copy the filtered data to another sheet and they paly around there. But while copying i want only visible cells to be copied to other sheet. which range to copy and paste is not in our control, as the range gets filtered out based on other input given by user...example if user filter A1 and J1 only A1 and J1 will shown and if other users filter B1 and M1 only this needs to be copied. Appreciate your help
    Wednesday, July 25, 2012 4:31 PM
  • Try changing the line

    Set Rang = Selection.SpecialCells(xlCellTypeVisible)

    to

    Set Rang = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)


    Regards, Hans Vogelaar

    Wednesday, July 25, 2012 4:47 PM
  • It says  Object doesnt support propery or method.

    Sample Code:

    I have hidden one row in spreadh sheet.. and then executed below code. Got error saying Object doestn

    Sub test()

    Dim rang as Range

    Set rang = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
    rang.Copy
    Sheets("sheet2").Range("B2").Paste            ' Error at this line

    End Sub

    Wednesday, July 25, 2012 5:37 PM
  • Paste is a worksheet level method. Try

    rang.Copy Sheets("sheet2").Range("B2")

    or use  

    rang.Copy
    Sheets("sheet2").Range("B2").PasteSpecial xlPasteAll        


    HTH, Bernie

    Wednesday, July 25, 2012 5:45 PM
  • Doestn work..   :(

    trying to copy sheet 1 data with out hidden rows to Sheet 2 ..

    Sub test()

    Dim rang As Range


    Set rang = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)

    rang.Paste Sheets("Sheet2").Range("B2")

    End Sub

    Wednesday, July 25, 2012 6:05 PM
  • Hans- Any clue how to fix this ?

    Wednesday, July 25, 2012 6:56 PM
  • Robo,

    Try this,

    Dim rang As Range
    
    Set rang = Range("A1:Z" & _
        Cells.SpecialCells(xlCellTypeLastCell).Row).SpecialCells(xlCellTypeVisible)
    
    rang.Copy Sheets("Sheet2").Range("B2")

    • Proposed as answer by Mike7952 Thursday, July 26, 2012 9:14 PM
    • Unproposed as answer by Mike7952 Thursday, July 26, 2012 9:14 PM
    • Proposed as answer by Leo_Gao Monday, July 30, 2012 1:37 AM
    • Marked as answer by Leo_Gao Thursday, August 2, 2012 1:07 AM
    Wednesday, July 25, 2012 7:01 PM
  • Fantastic this worked. Thanks Mike.

    Other question, my macro has Submit button, first time it runs faster and if i click once again it takes too long to do the Job. I dont believe this is code performance issue because if is code performance issue it will not execute faster for the first time too but something wrong. I have disabled all Applciation.screen updating to flase etc..any clue ?

    Wednesday, July 25, 2012 8:51 PM
  • HI Robo,

    Could you please share the macro has the Submit button in the forum? I assume that some commands in your macro after the first execution cause subsequently slowing down. The code will be beneficial for us to do further research on your issue.

    Thanks,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Friday, July 27, 2012 5:28 AM