excelvbacopyrowhidden

Copying data into a worksheet when the destination has hidden rows


Here's a simplified version of my problem. I’m using a macro to copy a range of doctor's names in A1:A10 to multiple other worksheets. Some of the worksheets I’m pasting into have hidden rows, so not all of the 10 pasted names are visible. For example, when I paste the 10 names into worksheet 2 which has rows 3 and 4 hidden, doctor 3 and doctor 4 aren't visible. Here's some code:

Sub Macro1()
    Range("A1:A10").Copy
    Sheets("2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("3").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Here's what worksheet 2 looks like after the paste:

This image shows how doctor 3 and doctor 4 are not visible

I need the macro to skip the hidden rows when the 10 names get pasted so that they all are visible. I haven't been able to find an answer anywhere. Thanks.


Solution

  • Place this code in the sheet's code module where the data to copy are. The new sub checks the hidden state of the rows of the target sheets and copy only to the visible cells the source column.

    Sub Macro1()
        'Range("A1:A10").Copy
        Sheets("sheet3").Select
        ActiveSheet.Range("A1").Select
        with_hidden_rows
        'ActiveSheet.Paste
        Sheets("sheet4").Select
        ActiveSheet.Range("A1").Select
        with_hidden_rows
        'ActiveSheet.Paste
        Application.CutCopyMode = False
    End Sub
    
    Sub with_hidden_rows()
    
    Dim rows As Long
    Dim rng As Range
    Set rng = Range("A1:A10")   'the column to copy
    rows = rng.rows.Count
    rowpointer = 1
    For i = 1 To rows
      Do While ActiveSheet.Cells(rowpointer, ActiveCell.Column).rows.Hidden = True
        rowpointer = rowpointer + 1
      Loop
        ActiveSheet.Cells(rowpointer, ActiveCell.Column) = Cells(i, rng.Column)
        rowpointer = rowpointer + 1
      
    Next i
    
    End Sub