Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping and copying from a list
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping and copying from a list
On Apr 19, 9:58 am, "bestie22 via OfficeKB.com" <u26850@uwe wrote:
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 -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200704/1 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping and copying from a list
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" <u26850@uwe je napisao u poruci interesnoj grupi:70edb2bf89474@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping and copying from a list
Thanks..........I think its sorted now....
PMC1 wrote: Hi, [quoted text clipped - 67 lines] -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200704/1 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping and copying | Excel Programming | |||
Looping through my worksheet and copying cells | Excel Programming | |||
looping and copying from onesheet to othersheet | Excel Programming | |||
Looping down list and each time copying to another worksheet | Excel Programming | |||
Looping down list and each time copying to another worksheet | Excel Programming |