Copies all the numeric constants in the active sheet to blocks in the sheet named Constants, leaving an empty row between each block : SpecialCells « Excel « VBA / Excel / Access / Word
Copies all the numeric constants in the active sheet to blocks in the sheet named Constants, leaving an empty row between each block
Sub CopyAreas() Dim rng As range, rngDestination As range
Set rngDestination = Worksheets("Sheet1").range("A1")
For Each rng In cells.SpecialCells(xlCellTypeConstants, xlNumbers).Areas
rng.copy Destination:=rngDestination
Set rngDestination = rngDestination.offset(rng.Rows.count + 1)
Next rng End Sub