Copying data into a worksheet when the destination has hidden rows

Question

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.

Answer 1

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