Need Help - Copy a Cell

[Donnie Stone]

I'm looking for a simpler way of doing the following:

Sub CopyCell()
Sheets("SHEET1").Range("A1").Copy Sheets("SHEET2").Range("A1")
Sheets("SHEET1").Range("A2").Copy Sheets("SHEET2").Range("A1")
Sheets("SHEET1").Range("A3").Copy Sheets("SHEET2").Range("A1")
Sheets("SHEET1").Range("A4").Copy Sheets("SHEET2").Range("A1")
Sheets("SHEET1").Range("A5").Copy Sheets("SHEET2").Range("A1")
End Sub

The data in sheet1, column A is dynamic, it may contain 5 records or 500.
Instead of adding additional lines of code, is there a routine that can be
used that provides the same results?

Thanks,

Donnie

 

[Mike Fogleman]

Clarification needed. It appears you are trying to copy 5 different cells on
sheet1 all to "A1" of sheet2. This is not usually done. Do you want sheet2
to be a copy of sheet1?

 

[Donnie Stone]

Mike,

I'm wanting to copy one cell at a time (ascending order) from sheet1, column
A to sheet2, A1. I do not want a copy of sheet1.

Donnie

 

[pk]

I'm not sure what your criteria are, but the following
will do what you want, acting on any non-blank cell
encountered. Write lines three and four on one row in
your module:

For Each cell In ActiveSheet.UsedRange.Columns(1).Rows
If cell.FormulaR1C1 <> "" Then
cell.Copy Destination:=Sheets("Sheet2").Range
("A1")
End If
Next cell

Hope this helps...

 

[Donnie Stone]

PK,

Thanks for the help. It works great!

I'm not sure what your criteria are, but the following
will do what you want, acting on any non-blank cell
encountered. Write lines three and four on one row in
your module:

For Each cell In ActiveSheet.UsedRange.Columns(1).Rows
If cell.FormulaR1C1 <> "" Then
cell.Copy Destination:=Sheets("Sheet2").Range
("A1")
End If
Next cell

Hope this helps...

 

[Donnie Stone]

PK,

I have a header in row 1. Is it possible to modify the code so it excludes
it?