Private Sub UserForm_Activate()
Dim rng As Range
Dim iRow As Integer
Dim iCol As Integer
Set rng = Sheets("Sheet1").UsedRange
For iRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
Spreadsheet1.Cells(iRow, iCol) = rng.Cells(iRow, iCol)
Next iCol
If rng.Rows(iRow).Hidden = True Then _
Spreadsheet1.Rows(iRow).EntireRow.Hidden = True
Next iRow
End Sub


Hth,
Merjet