Looping and copying from a list

[bestie22 via OfficeKB.com]

Hi,

I need a fresh pair of eyes so hopefully someone can help....

I have a list of data and what I want to do is copy the values in coloumn A
pasting them in cell D4 (other actions area then performed). I have been
trying to use the active cell method but the problem I have is that when I
paste the value in D4 this then becomes the active cell......

Is there any way to right the value of Active cell to a varianble and the
call that variable after pasting?
Or would I be better off trying to use numbers to reference the cells.....

I've tried both approaches and have used the following code without much
success....Any suggestions..

Using the ActiveCell method...............

Sub CycleList()
Dim Cellstore As Integer
Dim lRow As Long

'error trap
On Error GoTo Etrap

Worksheets("CODES").Range("A6").Activate

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.Select
Selection.Copy
Cellstore = ActiveCell.Address
Range("D2").Select
ActiveSheet.Paste
Range(ActiveCell.Address).Select

Loop

Etrap:

Beep

Exit Sub

End Sub


Using the Number method...............

Sub CycleListNumeric()
'error trap
On Error GoTo Etrap

Number = 6
Do
Range("A,Number").Select
Selection.Copy
Range(D4).Select
ActiveSheet.Paste
Number = Number + 1
Loop Until Number = 15

Etrap:

Beep

Exit Sub

End Sub

 

[PMC1]

Hi,

I need a fresh pair of eyes so hopefully someone can help....

I have a list of data and what I want to do is copy the values in coloumn A
pasting them in cell D4 (other actions area then performed). I have been
trying to use the active cell method but the problem I have is that when I
paste the value in D4 this then becomes the active cell......

Is there any way to right the value of Active cell to a varianble and the
call that variable after pasting?
Or would I be better off trying to use numbers to reference the cells.....

I've tried both approaches and have used the following code without much
success....Any suggestions..

Using the ActiveCell method...............

Sub CycleList()
Dim Cellstore As Integer
Dim lRow As Long

'error trap
On Error GoTo Etrap

Worksheets("CODES").Range("A6").Activate

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.Select
Selection.Copy
Cellstore = ActiveCell.Address
Range("D2").Select
ActiveSheet.Paste
Range(ActiveCell.Address).Select

Loop

Etrap:

Beep

Exit Sub

End Sub

Using the Number method...............

Sub CycleListNumeric()
'error trap
On Error GoTo Etrap

Number = 6
Do
Range("A,Number").Select
Selection.Copy
Range(D4).Select
ActiveSheet.Paste
Number = Number + 1
Loop Until Number = 15

Etrap:

Beep

Exit Sub

End Sub

Worksheets("CODES").Range("A6").copy
Destination:=Worksheets("CODES").Range("D4")

This will cut out the clipboard from the equation thus speeding up the
procedure (although this wont be noticable in this case as the data is
small) and also you don't have to "select" the destination cell


hth

Paul

 

[sali]

try this, as idea

sub aaaa
dim i as integer
for i=1 to 100
with activesheet
.range("d4").value=.range("a" & i).value
end with
next
end sub

 

[bestie22 via OfficeKB.com]

Thanks..........I think its sorted now....

Hi,

[quoted text clipped - 67 lines]
Worksheets("CODES").Range("A6").copy
Destination:=Worksheets("CODES").Range("D4")

This will cut out the clipboard from the equation thus speeding up the
procedure (although this wont be noticable in this case as the data is
small) and also you don't have to "select" the destination cell

hth

Paul