I've been using the following code to copy a range of cells to another
worksheet, appending each time.

Sheets("Order").Select
Range("R2:AP2").Select
Selection.Copy

Sheets("data").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

It's worked fine until I've directed it to another sheet, "data". Now,
I get a run time erroe 1004, App defined or Object defined error.

The first blank row in column A starts with cell A2. That's where it
should start. In A1 I have the word "Description". I've tried numbers,
even left A1 blank and still get the same error. Cells to the right of
A1 have other headings. If I do a manual Paste it works.

Thoughts?

I just discovered that if I put something in A2 it will start in A3. It
seems to have a problem with A2 for some reason, but not A3.

The End() function works on either the presence of data or the absence of
data. If your starting point contains data and the next cell, in your case
A2, is blank. It will stop because it believes it has reached the end of the
series. If you start in a blank cell and the next cell contains data, it
will stop for the same reason. It looks for all of the cells that are like
the one it starts in, either "" or = "". Does this help?

"mike" wrote:

I just discovered that if I put something in A2 it will start in A3. It
seems to have a problem with A2 for some reason, but not A3.

Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with them.

NickHK

"mike"
groups.com...
Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

When you say you directed it to another sheet, exactly what do you mean.
What changes did you make to the code you posted, or is the code you posted
the changed code?

"mike" wrote:

Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

Mike, your code works OK. Check it by stepping through one line at a time.
I think you might have part of a line of code below the line it should be
part of.

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, 'No attenuator here
SkipBlanks:= _
False, Transpose:=False

This should all be one line of code.

"mike" wrote:

Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

Nick,

Thanks but it works exactly the same. Stalls at the paste line unless I
put something in A2, then it starts at A3 ok.

NickHK wrote:
Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with them.

NickHK

"mike"
groups.com...
Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

JL,

The code got reformatted when I pasted to the forum.

I was copying to sheet"Build List" then I decided to copy to another
sheet. No other changes. So, I went into the code and just replaced
"Build List" with "data". That's all I did, promise...

The difference is in the number of nonempty cells before it gets to the
first empty cell. Apparently, it won't work if there's less than two
nonempty cells before the first empty cell. When sheet "data" is clean
and only the header row (row 1) is filled (with header titles in each
column), it stalls. When I go to row 2 (A2) and put something in it,
then it'll run fine, starting at cell A3.

??

JLGWhiz wrote:
Mike, your code works OK. Check it by stepping through one line at a time.
I think you might have part of a line of code below the line it should be
part of.

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, 'No attenuator here
SkipBlanks:= _
False, Transpose:=False

This should all be one line of code.

"mike" wrote:

Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

Mike,
Yes, but starting from A1, if there are no entries then what do you expect.
You are looking for the end of blank cells.

NickHK

"mike"
egroups.com...
Nick,

Thanks but it works exactly the same. Stalls at the paste line unless I
put something in A2, then it starts at A3 ok.

NickHK wrote:
Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with them.

NickHK

"mike"
groups.com...
Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

I'm actually looking for the end of nonblank cells (or the beginning of
blank cells).

That's how it was working before anyway.


NickHK wrote:
Mike,
Yes, but starting from A1, if there are no entries then what do you expect.
You are looking for the end of blank cells.

NickHK

"mike"
egroups.com...
Nick,

Thanks but it works exactly the same. Stalls at the paste line unless I
put something in A2, then it starts at A3 ok.

NickHK wrote:
Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with them.

NickHK

"mike"
groups.com...
Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

Still no luck. Anybody want to see the file?

mike wrote:
I'm actually looking for the end of nonblank cells (or the beginning of
blank cells).

That's how it was working before anyway.


NickHK wrote:
Mike,
Yes, but starting from A1, if there are no entries then what do you expect.
You are looking for the end of blank cells.

NickHK

"mike"
egroups.com...
Nick,

Thanks but it works exactly the same. Stalls at the paste line unless I
put something in A2, then it starts at A3 ok.

NickHK wrote:
Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with them.

NickHK

"mike"
groups.com...
Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

Hi Mike

I can't explain why it works for you in one scenario rather than
another.
However, I always tend to use xlUp rather than xlDown when finding the
lastrow, just in case there are any blanks

Something like
Range("A" & (Cells(Rows.Count, 1).End(xlUp).Row) + 1).Select
should produce the correct result whether you just have data in the
header row, or any rows beneath.

The first 1 in the line refers to column 1.
The second 1 is merely adding 1 to the cell count, to take the selection
to the first line after that in which data was found.

I am sure others will have far more efficient methods, but this works
for me.

--
Regards

Roger Govier


"mike" wrote in message
ups.com...
Still no luck. Anybody want to see the file?

mike wrote:
I'm actually looking for the end of nonblank cells (or the beginning
of
blank cells).

That's how it was working before anyway.


NickHK wrote:
Mike,
Yes, but starting from A1, if there are no entries then what do you
expect.
You are looking for the end of blank cells.

NickHK

"mike"
egroups.com...
Nick,

Thanks but it works exactly the same. Stalls at the paste line
unless I
put something in A2, then it starts at A3 ok.

NickHK wrote:
Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with
them.

NickHK

"mike"
groups.com...
Well I'm wanting to start at the first 'different' cell, that
is the
first empty cell.

Apparently something about there only being one non-empty cell
in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste
to. It
doesn't work until I have at least two cells in the range that
are not
empty. If only one is not empty, it freaks. I don't know why.
I can add
an extra row to get it to work but it's really bugging me as
to why it
doesn't just work with only one nonempty cell.