excle汇总vba代码
时间: 2023-09-21 07:11:12 浏览: 51
以下是一个简单的VBA代码示例,用于将多个工作簿中的数据汇总到一个主工作簿中:
Sub SummarizeData()
Dim MasterWorkbook As Workbook
Dim CurrentWorkbook As Workbook
Dim DataSheet As Worksheet
Dim SummarySheet As Worksheet
Dim LastRow As Long
Set MasterWorkbook = ThisWorkbook
Set SummarySheet = MasterWorkbook.Sheets("Summary")
'Loop through all workbooks in folder
Application.ScreenUpdating = False
ChDir "C:\Data"
Fname = Dir("*.xlsx")
Do While Fname <> ""
Set CurrentWorkbook = Workbooks.Open(Fname)
Set DataSheet = CurrentWorkbook.Sheets("Data")
'Copy data to master workbook
LastRow = SummarySheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
DataSheet.Range("A2:D100").Copy Destination:=SummarySheet.Range("A" & LastRow)
CurrentWorkbook.Close SaveChanges:=False
Fname = Dir
Loop
Application.ScreenUpdating = True
End Sub
该代码将从指定文件夹中打开所有工作簿,然后将每个工作簿中的名为“Data”的工作表的第2行到第100行中的四列数据复制到名为“Summary”的工作表中的下一行。您可以根据您的具体需要进行更改。