excel合併sheet

呼叫巨集 alt+F11

Sub Worksheet_Total()
    Dim rowNum As Integer
    Dim i, k As Integer
 
    Set wsNewWorksheet = ActiveWorkbook.Worksheets.Add(before:=Worksheets(1))
    wsNewWorksheet.Name = "data_total"
    rowNum = 1
    For k = 1 To ActiveWorkbook.Worksheets.Count
        i = 2
        Do While ActiveWorkbook.Worksheets(k).Cells(i, 1).Value <> ""
            wsNewWorksheet.Cells(rowNum, 1).Value = ActiveWorkbook.Worksheets(k).Name
            For j = 2 To 5
                wsNewWorksheet.Cells(rowNum, j).Value = ActiveWorkbook.Worksheets(k).Cells(i, j).Value
            Next j
            rowNum = rowNum + 1
            i = i + 1
        Loop
    Next k
End Sub

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25323853/viewspace-707764/，如需轉載，請註明出處，否則將追究法律責任。