excel - How to move to next blank cell? -

i have data on multiple sheets in workbook want copied 1 sheet in same workbook. when run macro, start deleting current data in "ipage data export" sheet , replacing data other sheets.

i want process occur 1 column @ time since may not bring on everything. right trying learn how 1 column.

i able copy of contents of column 1 sheet, when moves next sheet, overwrites existing data. in end, 1 sheets worth of data copied.

here 4 problems:

1. how make clear data on sheet before running routine?

2. how can make start each copy function @ bottom of row (i.e. after last cell value)? have tried many of suggestions on , other boards without success. admit not experienced in this.

3. how can make copy particular column (currently seems default a.

4. how can concatenate multiple columns during paste function? i.e. if want insert: a2&", "b2 instead of a2

sub combinedata()
      dim sht worksheet      each sht in activeworkbook.worksheets         if sht.name <> "ipage data export"             sht.select             range("c:c").copy             sheets("ipage data export").select             activesheet.paste         else         end if     next sht  end sub 

how make clear data on sheet before running routine?

sht.cells.clearcontents   

how can make start each copy function @ bottom of row (i.e. after last cell value)? have tried many of suggestions on , other boards without success. admit not experienced in this.

range("c" & rows.count).end(xlup).offset(1, 0) 

in detail:

• rows.count return number of rows in sheet, in legacy style *.xls workbooks return number 65,536. therefore "c" & rows.count same c65536

• range("c" & rows.count).end(xlup) same going c65536 , pressing ctrl + ↑ - command end(xldirection) tells program go last cell in range. in case, end @ last cell containing data in column c.

• .offset(1, 0) means want return range offset amount of rows and/or columns. vba uses rc (rows columns) references, whenever see offset() function 2 numbers being passed arguments, relates row, , column, in order. in case, want cell 1 row below last cell referenced.

• all-in-all phrase range("c" & rows.count).end(xlup).offset(1, 0) means go last cell in column c, go until hit last cell data, , return cell below - next empty cell.

how can make copy particular column (currently seems default a.

range("c:c").copy destination:=sheets("ipage data export").range("a:a") 

you can pass destination argument in same line , bypass clipboard (faster , cleaner)

how can concatenate multiple columns during paste function? i.e. if want insert: a2&", "b2 instead of a2

lets wanted reference column a, b, , f - use:

range("a1, b1, f1").entirecolumn   

---

to summarise, streamline existing code (untested):

sub combinedata()  dim sht worksheet     each sht in activeworkbook.worksheets         if sht.name <> "ipage data export"             sht.range("c1:c" & cells(sht.rows.count, 3).end(xlup).row).copy destination:=sheets("ipage data export").range("a:a")         end if     next  end sub