Excel Forum

Code: Select all

Sub test0()
    Dim rAll As Range, r As Range
    Dim rTgAll As Range, i As Integer
    With Worksheets("Sheet1")
        Set rAll = .Range("b:w").SpecialCells(xlCellTypeConstants, 1)
    End With
    i = 1
    With Worksheets("Sheet2")
        .Range("a:x").ClearContents
        Set rTgAll = .Range("b2:w20")
        For Each r In rAll
            rTgAll(i).Value = r.Value
            i = i + 1
        Next r
    End With
End Sub[code]