Excel Forum

Code: Select all

i = 0: application.screenupdating = false: application.calculation = xlcalculationmanual: application.displayalerts = false: set op = workbooks.add.worksheets(1): op.range("a1").resize(1,3).value = array("No","Sheet Name","Size (KB)"): set fs = createobject("scripting.filesystemobject"): thisworkbook.worksheets.add before:=thisworkbook.worksheets(1): cb = vba.replace(thisworkbook.fullname,".xl","_copy.xl"): for j = thisworkbook.worksheets.count to 2 step - 1: sn = thisworkbook.worksheets(j).name : thisworkbook.savecopyas cb: sz = fs.getfile(cb).size: thisworkbook.worksheets(j).delete: thisworkbook.savecopyas cb: i = i + 1: op.range("a" & op.rows.count).end(xlup).offset(1,0).resize(1,3).value = array(i,sn,format((sz - fs.getfile(cb).size)/1024,"0.00")): next j: op.range("a1:c1").entirecolumn.autofit: application.displayalerts = true: application.calculation = xlcalculationautomatic: application.screenupdating = true: vba.kill cb: thisworkbook.close false:⁣⁣⁣