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

"ian bartlett" wrote:

> 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.
>
>
>

Thanks vbapro

That looks like it should do the trick.
Bart

"vbapro" <(E-Mail Removed)> wrote in message
news:80877DC4-F1CD-4CE5-92E3-(E-Mail Removed)...
> 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
>
> "ian bartlett" wrote:
>
>> 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.
>>
>>
>>