excel学习库

一、Excel宏代码

Sub 汇总工作簿中表数据()
Dim sht As Worksheet, rng As Range, k&, trow&
Application.ScreenUpdating = False
trow = Val(InputBox("请输入标题的行数", "提醒"))
If trow < 0 Then MsgBox "标题行数不能为负数。", 64, "警告": Exit Sub
Cells.ClearContents
Cells.NumberFormat = "@"
For Each sht In Worksheets
If sht.Name <> ActiveSheet.Name Then
Set rng = sht.UsedRange
k = k + 1
If k = 1 Then
rng.Copy
[a1].PasteSpecial Paste:=xlPasteValues
Else
rng.Offset(trow).Copy
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).PasteSpecial Paste:=xlPasteValues
End If
End If
Next
[a1].Activate
Application.ScreenUpdating = True
MsgBox "数据合并完成，一共" & k & "张工作表", 0, "潮普哥带你学Excel"
End Sub 

二、WPS宏代码

function GetShtData(){
Cells.ClearContents();//清除整表旧数据
let aRes=[],k=0;
var qwe=InputBox("请输入标题的行数");
for(let sht of Worksheets){
if(sht.Name!=ActiveSheet.Name){
let aTemp = sht.UsedRange.Value2;
if(Array.isArray(aTemp)){
if(k++>0){aTemp.splice(0,qwe);};
aRes.push(...aTemp);
}
}
}
if(aRes!=false){
Range("a1").Resize(aRes.length,aRes[0].length).Value2=aRes;
}
MsgBox("数据合并完成，一共" + k +"张工作表",0,"潮普哥带你学Excel");
}