I have a header in row 1. Is it possible to modify the code so it excludes
it and starts executing in cell A2 for example?

"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

Donnie,

For Each cell In Intersect(ActiveSheet.UsedRange.Columns(1),
Range("2:65536"))

HTH,
Bernie


"Donnie Stone" wrote in message
...
I have a header in row 1. Is it possible to modify the code so it
excludes
it and starts executing in cell A2 for example?

"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

Bernie,

Thanks for your help. I made the changes you recommended and I'm getting
the following:

Run-time error '1004'
Application-defined or object defined error

Any idea what's causing this?

"Bernie Deitrick" wrote in message
...
Donnie,

For Each cell In Intersect(ActiveSheet.UsedRange.Columns(1),
Range("2:65536"))

HTH,
Bernie


"Donnie Stone" wrote in message
...
I have a header in row 1. Is it possible to modify the code so it
excludes
it and starts executing in cell A2 for example?

"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

Donnie,
If you're running the macro from a cutton on the sheet, you need to set the
TakeFocusOnClick property of the button to false. Otherwise, precede the
line with

Activesheet.Activate

and that should cure it.

HTH,
Bernie

"Donnie Stone" wrote in message
...
Bernie,

Thanks for your help. I made the changes you recommended and I'm getting
the following:

Run-time error '1004'
Application-defined or object defined error

Any idea what's causing this?

"Bernie Deitrick" wrote in message
...
Donnie,

For Each cell In Intersect(ActiveSheet.UsedRange.Columns(1),
Range("2:65536"))

HTH,
Bernie


"Donnie Stone" wrote in message
...
I have a header in row 1. Is it possible to modify the code so it
excludes
it and starts executing in cell A2 for example?

"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