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.

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.

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.

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.