finding first empty cell
 

I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

I believe that means that the active cell is the last row, so when you do
..End(xlDown) you get row 65536. You then try to offset that by one row which
returns the Runtime error. Try working from the bottom up:
With Worksheets("Master")
.Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
End With
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

The code will return an error if column A is blank or only the first row has
data. Try going from bottom to top.

Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste


--
Hope that helps.

Vergel Adriano


" wrote:

I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

You would have that problem if it went all the way to the bottom of the
worksheet.

Try coming up from the bottom.
set r= worksheets("Master").cells(rows.count,1).End(xlup) (2)

selection.copy r


also, avoid selecting and your code will run much faster.



--
Regards,
Tom Ogilvy



" wrote:

I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other
than master)

If it isn't then you need to qualify both:

Sub foo()
With Sheets("Master")
.Cells(.Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End with
End Sub

or just use

sheets("Master").Cells(rows.count,1).end( _
xlup).offset(1,0).Pastespecial xlPasteall

Just a thought that may eliminate some potential error/code failure
situations.

--
Regards,
Tom Ogilvy

"JW" wrote:

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

Tom, wouldn't you also need to qualify "Rows.Count"? If the activesheet is a
chart your code would error as well.

--
Charles Chickering

"A good example is twice the value of good advice."


"Tom Ogilvy" wrote:

Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other
than master)

If it isn't then you need to qualify both:

Sub foo()
With Sheets("Master")
.Cells(.Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End with
End Sub

or just use

sheets("Master").Cells(rows.count,1).end( _
xlup).offset(1,0).Pastespecial xlPasteall

Just a thought that may eliminate some potential error/code failure
situations.

--
Regards,
Tom Ogilvy

"JW" wrote:

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

On Sep 19, 8:48 am, Tom Ogilvy
wrote:
Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other
than master)

If it isn't then you need to qualify both:

Sub foo()
With Sheets("Master")
.Cells(.Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End with
End Sub

or just use

sheets("Master").Cells(rows.count,1).end( _
xlup).offset(1,0).Pastespecial xlPasteall

Just a thought that may eliminate some potential error/code failure
situations.

--
Regards,
Tom Ogilvy

"JW" wrote:
Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

Thanks all. I didn't realize that when my matrix only has data in the
header row the coding would go all the way to the bottom of the sheet
then try to offset it by 1 more row. I'll start at the bottom and
work up.

finding first empty cell
 

Certainly a valid consideration, but no, I have never had a chart as the
active sheet when I am copying to another worksheet and trying to paste a
value in the next available row - doesn't make much sense, but if it is a
problem for you, than by all means. It never hurts to code defensively.
In contrast the problem I highlight is extremely prevelant and often hard to
debug.

--
regards,
Tom Ogilvy


"Charles Chickering" wrote:

Tom, wouldn't you also need to qualify "Rows.Count"? If the activesheet is a
chart your code would error as well.

--
Charles Chickering

"A good example is twice the value of good advice."


"Tom Ogilvy" wrote:

Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other
than master)

If it isn't then you need to qualify both:

Sub foo()
With Sheets("Master")
.Cells(.Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End with
End Sub

or just use

sheets("Master").Cells(rows.count,1).end( _
xlup).offset(1,0).Pastespecial xlPasteall

Just a thought that may eliminate some potential error/code failure
situations.

--
Regards,
Tom Ogilvy

"JW" wrote:

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

Just a simple typo on my part. As for qualifying the sheet, I like to
play it safe. The code might not have been in a sheet module at all.
It may have been in a standard module and could therefore potentially
be ran on any number of sheets within the workbook.
Tom Ogilvy wrote:
Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other
than master)

If it isn't then you need to qualify both:

Sub foo()
With Sheets("Master")
.Cells(.Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End with
End Sub

or just use

sheets("Master").Cells(rows.count,1).end( _
xlup).offset(1,0).Pastespecial xlPasteall

Just a thought that may eliminate some potential error/code failure
situations.

--
Regards,
Tom Ogilvy

"JW" wrote:

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.

finding first empty cell
 

Sorry if I stepped on your toes. I was trying to help the OP avoid having
to figure out a transient and hard to debug problem.


--
Regards,
Tom Ogilvy




"JW" wrote:

Just a simple typo on my part. As for qualifying the sheet, I like to
play it safe. The code might not have been in a sheet module at all.
It may have been in a standard module and could therefore potentially
be ran on any number of sheets within the workbook.
Tom Ogilvy wrote:
Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other
than master)

If it isn't then you need to qualify both:

Sub foo()
With Sheets("Master")
.Cells(.Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End with
End Sub

or just use

sheets("Master").Cells(rows.count,1).end( _
xlup).offset(1,0).Pastespecial xlPasteall

Just a thought that may eliminate some potential error/code failure
situations.

--
Regards,
Tom Ogilvy

"JW" wrote:

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub
wrote:
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I
have used before):

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Now, I keep getting a run time error on the selection line. What am I
doing wrong?

Thanks.