macro to cut and paste

  • Thread starter Thread starter Chuck W
  • Start date Start date
C

Chuck W

Hi, I trying to write my first macro and having problems I have a
list in column A that I want to cut and copy to column C but I want to
have the results on every other row. I'm also trying to learn to do
loops. I used the "Record Macro" feature to do the first two items. I
want to add a loop to this code. Here it is:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/7/2003 by
'
' Keyboard Shortcut: Ctrl+c
'
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Range("A2").Select
Selection.Cut
Range("C3").Select
ActiveSheet.Paste
End Sub

1. I think that I must change ranges from specific cells "C1" to
something else so that macro will nolt return there but I don't know
what.
2. Where do I put the "For" and what do I put after it .
3. using Do until I tried "i"1 to 50 but that didn't work for me.

As you can see I am completely lost. I want not only to solve this
problem but to learn enough about them to apply them to similar
situations.

Thanks,

Chuck
 
Chuck

Dim cRows As Long, i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "A").Copy Destination:=Cells(i * 2 - 1, "C")
Next i

Some explanation.

The for loop is looking to go through al cells in column A and process them
starting at 1, hence
For i = 1 To

It knows where the end of data is by the
Cells(Rows.Count, "A").End(xlUp).Row
which goes to the bottom of the worksheet in column A (Rows.Count, "A")),
works up until the first non-empty cell it finds, which is the last in the
column (.End(xlUp)), and gets the row as it loop end-point (.Row).

It then Copies each cell in column A depending on the loop counter
Cells(i,"A").Copy
and pastes it
Destination:=
to a row calculated as twice the loop counter minus 1 (1:=2, 2-= 3, 3-= 5,
etc) within column C
Cells(i*2-1,"C")

Then just get next item
Next i
 
Back
Top