获取工作簿下所有工作簿内每个工作表数据汇总到指定工作表

抱着轮胎压马路

于 2022-09-21 19:21:58 发布

阅读量349

点赞数

分类专栏： VBA 文章标签：经验分享

本文链接：https://blog.csdn.net/m0_73280369/article/details/126978997

版权

VBA 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

Sub 转移数据()
    On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim s, i, k, wb As Workbook, ws As Worksheet, rng As Range
    Sheet1.[A2:AR10000].Clear
    s = Dir(ThisWorkbook.Path & "\" & "*.xlsx")
    Do
        If s <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & s)
            For Each ws In wb.Sheets
                If ThisWorkbook.Sheets("库存数据").[A1] = "" Then
                    ws.Cells.Copy ThisWorkbook.Sheets("库存数据").[A1]
                Else
                    
                    ws.Range("A2", ws.Cells(ws.Range("A1").CurrentRegion.Rows.Count, ws.Range("A1").CurrentRegion.Columns.Count)).Copy ThisWorkbook.Sheets("库存数据").Cells(ThisWorkbook.Sheets("库存数据").Range("A1").CurrentRegion.Rows.Count + 1, 1)
                End If
                
            Next ws
            wb.Close
        End If
        s = Dir
    Loop Until s = ""
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub