Copy to next blank row

D

Defoes Right Boot

Hi

I have a few cells of data which I need to copy to the next blank row of
another sheet. I can find the row number required by doing this :

Dim Rownumber As Integer
If Range("A20").Value = "" Then Rownumber = 20
If Range("A19").Value = "" Then Rownumber = 19
If Range("A18").Value = "" Then Rownumber = 18
If Range("A17").Value = "" Then Rownumber = 17
If Range("A16").Value = "" Then Rownumber = 16

But I am then having problems with defining the destination to copy to. I
have tried using :

Worksheets("CURRENT").Range("B2").Copy _
Destination:=Worksheets("Balance Order Form").Cells(2, Rownumber)

but I keep getting Runtime Error '1004 ' Application defined or object
defined error, and I don't know what that means or how I should get round it.

Can someone help? Many thanks

Phil
 
D

Dave Peterson

I'd check to see what RowNumber was right before the copy portion:

msgbox rownumber

If A16:A20 is all filled in, then what should happen?
 
B

Bob Phillips

It worked in my test with empty cells in the range A16:A20. But you get the
error if they all have data as rownumber is not set.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Defoes Right Boot

Thanks for suggestions. I have checked rownumber prior to attempting the
copy, and by giving rownumber an initial value I have found that rownumber is
not getting changed by any of the if...then code - but I can't see why, as
column A is totally blank at present.

I have also got a line which craps out of the macro with an error message
for the user if all the available rows are already filled - that bit at least
works OK!!!

Also the copying is not happening even when I give rownumber a value
manually although there is no error coming up.

I'm not a VB expert by any means so getting horribly confused by all this,
anyone got any further ideas?

Phil
 
T

Tom Ogilvy

try it this way

Dim Rownumber As Integer
If Trim(Range("A20").Value) = "" Then Rownumber = 20
If Trim(Range("A19").Value) = "" Then Rownumber = 19
If Trim(Range("A18").Value) = "" Then Rownumber = 18
If Trim(Range("A17").Value) = "" Then Rownumber = 17
If Trim(Range("A16").Value) = "" Then Rownumber = 16

Also, this assumes the activesheet is the sheet you are checking. However,
you use the information to copy to a specific sheet, so perhaps you just
need


Dim Rownumber As Integer
With Worksheets("Balance Order Form")
If .Range("A20").Value = "" Then Rownumber = 20
If .Range("A19").Value = "" Then Rownumber = 19
If .Range("A18").Value = "" Then Rownumber = 18
If .Range("A17").Value = "" Then Rownumber = 17
If .Range("A16").Value = "" Then Rownumber = 16
End With
 
D

Defoes Right Boot

Tom, Dave & Bob - thanks very much for your help... that combined with me
spotting a couple of stupid errors I made has got me sorted. Much appreciated
gentlemen.

Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top