copy from sheet 1 to sheet 2 values

  • Thread starter Thread starter ian bartlett
  • Start date Start date
I

ian bartlett

Hi:
My problem, on sheet 1 I have 6 defined named ranges
range1,range2 etc.
I would like to copy them to sheet 2 starting at row 2 what would be the
most efficient way to do this.

thanks Bart.
 
you can use something like

Sub test()
Dim RangeNames As Variant
Dim RangeIndex As Long
Dim LastRow As Long
Const ShName1 = "Sheet1"
Const ShName2 = "Sheet2"

RangeNames = Array("Range1", "Range2", "Range3")

LastRow = 1
For RangeIndex = LBound(RangeNames) To UBound(RangeNames)
Worksheets(ShName1).Range(RangeNames(RangeIndex)).Copy _
Destination:=Worksheets(ShName2).Cells(LastRow + 1, 1)

LastRow = LastRow +
Worksheets(ShName1).Range(RangeNames(RangeIndex)).Rows.Count
Next RangeIndex

End Sub
 
Back
Top