当前位置：首页 > news >正文

WPS宏实现Sheet页拆分功能

news 来源：原创 2024/9/19 8:50:09

源表格首列名称中一样的分别创建该名称的Sheet页，首先把首列复制导致Sheet2页，根据去重后的值创建新的Sheet页，把源表格数据复制到新建的Sheet页，遍历删除不需要的留下需要的就完成了。

function JIn521() {

    //设置工作表对象
    var ws1 = ThisWorkbook.Worksheets('Sheet1');
    //Sheet1页首列复制到Sheet2页并去重
    var lastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row;
    var sheet1Range1 = ws1.Range(ws1.Cells(1, 1), ws1.Cells(lastRow, 1));
    Worksheets.Add();
    ActiveSheet.Name = "Sheet2"
    var ws2 = ThisWorkbook.Worksheets('Sheet2');
    sheet1Range1.Copy(ws2.Cells(1, 1));
    ws2.Columns(1).EntireColumn.RemoveDuplicates(1, xlYes);

    //遍历Sheet2页值创建其余Sheet页
    var st2lastRow = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row;
    var worksheetObjects = {};
    for(var z = 0; z<st2lastRow-1;z++){
        var sheet2Range2ItemValue = ws2.Cells(z+2, 1).Value2;
        var newWorksheet = Worksheets.Add();
        newWorksheet.Name = sheet2Range2ItemValue;
        worksheetObjects[sheet2Range2ItemValue] = newWorksheet;

    //Sheet1页复制到其他页
    var lastCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column;
    var sheet1Range2 = ws1.Range(ws1.Cells(1,1),ws1.Cells(lastRow,lastCol));
    sheet1Range2.Copy(worksheetObjects[sheet2Range2ItemValue].Cells(1, 1));

    //获取其他页数据及地址
    var arrow = new Array();
    var st5LastRow = worksheetObjects[sheet2Range2ItemValue].Cells(worksheetObjects[sheet2Range2ItemValue].Rows.Count, 1).End(xlUp).Row;

    for(var j = 0 ; j < st5LastRow ; j++){
         arrow[j]=j+1;
    }

    var arrow1 = new Array();

    for (let x = 0; x < arrow.length; x++) {
        var a = worksheetObjects[sheet2Range2ItemValue].Range("A"+arrow[x]).Value2;
        var b = worksheetObjects[sheet2Range2ItemValue].Range("A"+arrow[x]).Address();
        arrow1.push(a+":"+b);
        }

    //遍历删除
    for(let i = 0;i<arrow1.length;i++){
        array = arrow1[i].split(':');

        if(array[0] != sheet2Range2ItemValue && array[0]!= "地市"){
            worksheetObjects[sheet2Range2ItemValue].Range(array[1]).EntireRow.Clear();
            }
        }

    var st5LastRow1 =worksheetObjects[sheet2Range2ItemValue].Cells(worksheetObjects[sheet2Range2ItemValue].Rows.Count, 1).End(xlUp).Row;
    for(let y = st5LastRow1; y>0; y--){
        if(Application.WorksheetFunction.CountA(worksheetObjects[sheet2Range2ItemValue].Rows(y))==0){
            worksheetObjects[sheet2Range2ItemValue].Rows(y).Delete();
        }
        }
    }
    //删除源表格Sheet2页,设置不弹出是否删除的提示框，执行删除完毕后开启
    Application.DisplayAlerts = false;
    ws2.Delete();
    Application.DisplayAlerts = true;
}