excel - VBA code to combine multiple worksheets mostly works, but is producing unexpected results -

excel - VBA code to combine multiple worksheets mostly works, but is producing unexpected results -

scenario: workbook have variable number of worksheets, each variable number of populated cells, commonly in column not necessarily. there no header row. want combine worksheets single "master" worksheet, after perform various shenanigans later on. have "mostly" works, reason leaves row 1 blank, not want. suppose delete empty row, seems awfully cheesy , doesn't solve underlying problem, don't understand why it's doing this.

code:

private sub cmdfinalize_click() dim wb workbook dim ws worksheet dim final worksheet dim rng range dim colcount integer set wb = activeworkbook set final = wb.worksheets.add(after:=wb.worksheets(wb.worksheets.count)) final.name = "final" set ws = wb.worksheets(1) colcount = ws.cells(1, 255).end(xltoleft).column each ws in wb.worksheets if ws.index = wb.worksheets.count exit end if set rng = ws.range(ws.cells(1, 1), ws.cells(65536, 1).end(xlup).resize(, colcount)) final.cells(65536, 1).end(xlup).offset(1).resize(rng.rows.count, rng.columns.count).value = rng.value next ws end sub

i assumed offset(1) causing problem, removing exclusively causes last row item of every worksheet overwritten first line of next 1 when gets glued on 'master' worksheet.

you need utilize offset(1) sec , after worksheet.

following code utilize flag indicate processing worksheet first or not.

private sub cmdfinalize_click() dim wb workbook dim ws worksheet dim final worksheet dim rng range dim colcount integer dim firstworksheetflag boolean 'flag indicate first worksheet in process firstworksheetflag = true ' flag true @ begin set wb = activeworkbook set final = wb.worksheets.add(after:=wb.worksheets(wb.worksheets.count)) final.name = "final" set ws = wb.worksheets(1) colcount = ws.cells(1, 255).end(xltoleft).column each ws in wb.worksheets if ws.index = wb.worksheets.count exit end if set rng = ws.range(ws.cells(1, 1), ws.cells(65536, 1).end(xlup).resize(, colcount)) if firstworksheetflag 'offset(1) not used. final.cells(65536, 1).end(xlup).resize(rng.rows.count, rng.columns.count).value = rng.value firstworksheetflag = false ' first worksheet has been processed, next not first. else 'second or later worksheet, offset(1) used. final.cells(65536, 1).end(xlup).offset(1).resize(rng.rows.count,rng.columns.count).value = rng.value end if next ws end sub

excel vba excel-vba