Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognize first emtpy Cell in column
How can i code to copy range (which is not always going to be the same from one book.sheet to another. The code would have to look for th first emtpy cell in the latter book, and thenn paste. Currently i select all cells (which i dont want, i need to select al data cells without header row) and paste in new book after selectin range. Range is dynamic , so i have to code to look for first emtp cell where i need to paste. any ideas. Thanx [color=RoyalBlue][color=Blue]ActiveSheet.Cells.Select ActiveSheet.Cells.EntireColumn.AutoFit ActiveSheet.Range("c1").Sort _ Key1:=ActiveSheet.Columns("c"), _ Header:=xlYes ActiveSheet.Cells.Select Selection.Copy Workbooks.Open "C:\Documents and Settings\User\Desktop\ALLTIE.xls", False AppActivate "Microsoft Excel"ActiveSheet.Cells.Select ActiveSheet.Past -- JohnD ----------------------------------------------------------------------- JohnDK's Profile: http://www.excelforum.com/member.php...nfo&userid=718 View this thread: http://www.excelforum.com/showthread.php?threadid=48240 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognize first emtpy Cell in column
if it's also the lastrow in the column, use this
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row this assume it's sheet1 and column A, just adjust to your needs then paste to range("A" & lastrow) -- Gary "JohnDK" wrote in message ...[color=blue] How can i code to copy range (which is not always going to be the same) from one book.sheet to another. The code would have to look for the first emtpy cell in the latter book, and thenn paste. Currently i select all cells (which i dont want, i need to select all data cells without header row) and paste in new book after selecting range. Range is dynamic , so i have to code to look for first emtpy cell where i need to paste. any ideas. Thanx [color=RoyalBlue]ActiveSheet.Cells.Select ActiveSheet.Cells.EntireColumn.AutoFit ActiveSheet.Range("c1").Sort _ Key1:=ActiveSheet.Columns("c"), _ Header:=xlYes ActiveSheet.Cells.Select Selection.Copy Workbooks.Open "C:\Documents and Settings\User\Desktop\ALLTIE.xls", , False AppActivate "Microsoft Excel"ActiveSheet.Cells.Select ActiveSheet.Paste -- JohnDK ------------------------------------------------------------------------ JohnDK's Profile: http://www.excelforum.com/member.php...fo&userid=7184 View this thread: http://www.excelforum.com/showthread...hreadid=482406 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognize first emtpy Cell in column
what column is the empty cell going to be in? Or is this a variable that is unknown? Because you could do a loop to look for a empty cell and then have it copy the data over? Is this what you are looking for? -- dok112 ------------------------------------------------------------------------ dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581 View this thread: http://www.excelforum.com/showthread...hreadid=482406 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognize first emtpy Cell in column
it will be the 1st empty cell in column C, but then have to paste it in corresponding cell column A (A contains multiple empty cells). Similarly how do i copy a dynamic range for the origin sheet without header row? Thanks -- JohnDK ------------------------------------------------------------------------ JohnDK's Profile: http://www.excelforum.com/member.php...fo&userid=7184 View this thread: http://www.excelforum.com/showthread...hreadid=482406 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognize first emtpy Cell in column
try:
Dim rng As Range, wbk As Workbook ActiveSheet.UsedRange.EntireColumn.AutoFit ActiveSheet.Range("c1").Sort _ Key1:=ActiveSheet.Columns("c"), _ Header:=xlYes Set rng = ActiveSheet.UsedRange Set wbk = Workbooks.Open("C:\Documents and Settings\User\Desktop\ALLTIE.xls", , False) rng.Copy wbk.ActiveSheet.Cells Set rng = Nothing -- http://www.vba.com.tw/plog/ "JohnDK" wrote: [color=blue] How can i code to copy range (which is not always going to be the same) from one book.sheet to another. The code would have to look for the first emtpy cell in the latter book, and thenn paste. Currently i select all cells (which i dont want, i need to select all data cells without header row) and paste in new book after selecting range. Range is dynamic , so i have to code to look for first emtpy cell where i need to paste. any ideas. Thanx [color=RoyalBlue]ActiveSheet.Cells.Select ActiveSheet.Cells.EntireColumn.AutoFit ActiveSheet.Range("c1").Sort _ Key1:=ActiveSheet.Columns("c"), _ Header:=xlYes ActiveSheet.Cells.Select Selection.Copy Workbooks.Open "C:\Documents and Settings\User\Desktop\ALLTIE.xls", , False AppActivate "Microsoft Excel"ActiveSheet.Cells.Select ActiveSheet.Paste -- JohnDK ------------------------------------------------------------------------ JohnDK's Profile: http://www.excelforum.com/member.php...fo&userid=7184 View this thread: http://www.excelforum.com/showthread...hreadid=482406 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognize first emtpy Cell in column
Thanks all for the suggestions!!! WOW chijanzen - Thx. activesheet.usedrange works, however is there a way i can deselect the header row in selection.copy because it appends in another sheet to excisting data. Thanks again -- JohnDK ------------------------------------------------------------------------ JohnDK's Profile: http://www.excelforum.com/member.php...fo&userid=7184 View this thread: http://www.excelforum.com/showthread...hreadid=482406 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookups don't recognize text to column cells - why? | Excel Discussion (Misc queries) | |||
Active Hide Emtpy Cells | Excel Discussion (Misc queries) | |||
emtpy cell | Excel Programming | |||
insert amount into first emtpy cell | Excel Programming | |||
Hide row when cell is emtpy | Excel Programming |