Does anyone know how to achieve the following (as my do loop contstructions
are taking quite a while to execute and this would be far faster I am sure).

Column A contains dates which expand by one each day (funnily
enough). Basically I want to copy the dates from one sheet to another as
fast as possible, and then copy a column of numbers into the adjacent column
(I wnat to copy one column over each time so offset will probably do me when
I
get the syntax).

My idea is to define the column of dates as a named range
and then just say "the same range on sheet B is to equal the named range on
sheet A"
the idea being that just picking up the dates as a block and plonking them
in the destination sheet is likely to be quicker than assigning the values
one at a time as i do now using a do loop construction.

Would appreciate any pointers, kind regards, Mark

Hi,


Try something like this which copies from SheetA To SheetB without using
named ranges. Change ranges as needed

Sub CopyRng()

Dim lastrow As Long, InRng As Range, OutRng As Range

lastrow = Worksheets("SheetA").Cells(Rows.Count, "A").End(xlUp).Row

Set InRng = Range("A2:A" & lastrow) ' Input dates starting in A2

Set OutRng = Worksheets("SheetB").Range("a2") ' <== Change as required

InRng.Copy OutRng

End Sub

HTH

"Mark Stephens" wrote:

Does anyone know how to achieve the following (as my do loop contstructions
are taking quite a while to execute and this would be far faster I am sure).

Column A contains dates which expand by one each day (funnily
enough). Basically I want to copy the dates from one sheet to another as
fast as possible, and then copy a column of numbers into the adjacent column
(I wnat to copy one column over each time so offset will probably do me when
I
get the syntax).

My idea is to define the column of dates as a named range
and then just say "the same range on sheet B is to equal the named range on
sheet A"
the idea being that just picking up the dates as a block and plonking them
in the destination sheet is likely to be quicker than assigning the values
one at a time as i do now using a do loop construction.

Would appreciate any pointers, kind regards, Mark

Better ....

Sub CopyRng()

Dim lastrow As Long, InRng As Range, OutRng As Range

Set OutRng = Worksheets("Sheet2").Range("a2") ' <== Change as required

With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set InRng = .Range("a2:A" & lastrow)
End With

InRng.Copy OutRng

End Sub


"Toppers" wrote:

Hi,


Try something like this which copies from SheetA To SheetB without using
named ranges. Change ranges as needed

Sub CopyRng()

Dim lastrow As Long, InRng As Range, OutRng As Range

lastrow = Worksheets("SheetA").Cells(Rows.Count, "A").End(xlUp).Row

Set InRng = Range("A2:A" & lastrow) ' Input dates starting in A2

Set OutRng = Worksheets("SheetB").Range("a2") ' <== Change as required

InRng.Copy OutRng

End Sub

HTH

"Mark Stephens" wrote:

Does anyone know how to achieve the following (as my do loop contstructions
are taking quite a while to execute and this would be far faster I am sure).

Column A contains dates which expand by one each day (funnily
enough). Basically I want to copy the dates from one sheet to another as
fast as possible, and then copy a column of numbers into the adjacent column
(I wnat to copy one column over each time so offset will probably do me when
I
get the syntax).

My idea is to define the column of dates as a named range
and then just say "the same range on sheet B is to equal the named range on
sheet A"
the idea being that just picking up the dates as a block and plonking them
in the destination sheet is likely to be quicker than assigning the values
one at a time as i do now using a do loop construction.

Would appreciate any pointers, kind regards, Mark

Thabks for that Toppers it looks much more elegant than what I have I will
give it a try

Kind regards, Mark


"Toppers" wrote in message
...
Better ....

Sub CopyRng()

Dim lastrow As Long, InRng As Range, OutRng As Range

Set OutRng = Worksheets("Sheet2").Range("a2") ' <== Change as required

With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set InRng = .Range("a2:A" & lastrow)
End With

InRng.Copy OutRng

End Sub


"Toppers" wrote:

Hi,


Try something like this which copies from SheetA To SheetB without using
named ranges. Change ranges as needed

Sub CopyRng()

Dim lastrow As Long, InRng As Range, OutRng As Range

lastrow = Worksheets("SheetA").Cells(Rows.Count, "A").End(xlUp).Row

Set InRng = Range("A2:A" & lastrow) ' Input dates starting in A2

Set OutRng = Worksheets("SheetB").Range("a2") ' <== Change as required

InRng.Copy OutRng

End Sub

HTH

"Mark Stephens" wrote:

Does anyone know how to achieve the following (as my do loop
contstructions
are taking quite a while to execute and this would be far faster I am
sure).

Column A contains dates which expand by one each day (funnily
enough). Basically I want to copy the dates from one sheet to another
as
fast as possible, and then copy a column of numbers into the adjacent
column
(I wnat to copy one column over each time so offset will probably do me
when
I
get the syntax).

My idea is to define the column of dates as a named range
and then just say "the same range on sheet B is to equal the named
range on
sheet A"
the idea being that just picking up the dates as a block and plonking
them
in the destination sheet is likely to be quicker than assigning the
values
one at a time as i do now using a do loop construction.

Would appreciate any pointers, kind regards, Mark