Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next blank row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next blank row
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? Defoes Right Boot wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next blank row
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) "Defoes Right Boot" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next blank row
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 "Bob Phillips" wrote: 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) "Defoes Right Boot" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next blank row
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 -- Regards, Tom Ogilvy "Defoes Right Boot" wrote in message ... 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 "Bob Phillips" wrote: 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) "Defoes Right Boot" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next blank row
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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Defoes Right Boot" wrote in message ... 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 "Bob Phillips" wrote: 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) "Defoes Right Boot" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cells become blank | New Users to Excel | |||
Copy, next blank row? | Excel Worksheet Functions | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
copy blank cells | Excel Discussion (Misc queries) | |||
Copy Until Blank | Excel Programming |