Need Help - Copy a Cell
 

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

Need Help - Copy a Cell
 

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" wrote in message
...
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

Need Help - Copy a Cell
 

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

"Mike Fogleman" wrote in message
...
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" wrote in message
...
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

Need Help - Copy a Cell
 

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...

-----Original Message-----
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


.

Need Help - Copy a Cell
 

PK,

Thanks for the help. It works great!

"pk" wrote in message
...
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...

-----Original Message-----
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


.

Need Help - Copy a Cell
 

PK,

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


"pk" wrote in message
...
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...

-----Original Message-----
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


.