如何用公式自动读取所有工作表名称，自动写在A列第三行及以下。

xtcx

如何用公式自动读取除本工作表（汇总表）以外的其他所有工作表名称，自动写在A列第三行及以下。

cnmlgb9998

cell () 可以，有点复杂 ，最好 VB

甘地降价

Sub 汇总单日收费()

    For I = 1 To ThisWorkbook.Worksheets.Count - 1
        ThisWorkbook.Worksheets(I).Activate
        lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
        orlastrow = Worksheets(ThisWorkbook.Worksheets.Count - 1).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
            If ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count - 1).Range("A" & orlastrow) = "合计" Then
                MsgBox "不可重复执行"
                Exit For
            End If

        Range("A" & lastrow + 1) = "合计"
        For j = 4 To lastrow - 1
        Range("D" & lastrow + 1) = Range("D" & j) + Range("D" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("e" & lastrow + 1) = Range("e" & j) + Range("e" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("F" & lastrow + 1) = Range("F" & j) + Range("F" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("G" & lastrow + 1) = Range("G" & j) + Range("G" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("H" & lastrow + 1) = Range("H" & j) + Range("H" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("I" & lastrow + 1) = Range("I" & j) + Range("I" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("J" & lastrow + 1) = Range("J" & j) + Range("J" & j + 1)
        Next j

        For j = 4 To lastrow - 1
        Range("K" & lastrow + 1) = Range("K" & j) + Range("K" & j + 1)
        Next j

    ThisWorkbook.Worksheets("汇总表").Range("A" & I + 2) = ThisWorkbook.Worksheets(I).Name
    ThisWorkbook.Worksheets(I).Range("d" & lastrow + 1).Copy Destination:=ThisWorkbook.Worksheets("汇总表").Range("B" & I + 2)
    ThisWorkbook.Worksheets(I).Range("e" & lastrow + 1).Copy Destination:=ThisWorkbook.Worksheets("汇总表").Range("c" & I + 2)
    ThisWorkbook.Worksheets(I).Range("f" & lastrow + 1).Copy Destination:=ThisWorkbook.Worksheets("汇总表").Range("d" & I + 2)

    Next I

    Application.GoTo Worksheets("汇总表").Range("A1"), Scroll:=True



End Sub




Sub 还原操作()


    For I = 1 To ThisWorkbook.Worksheets.Count - 1
        ThisWorkbook.Worksheets(I).Activate
        lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
        orlastrow = Worksheets(ThisWorkbook.Worksheets.Count - 1).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
            If ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count - 1).Range("A" & orlastrow) <> "合计" Then
                MsgBox "不可重复执行"
                Exit For
            End If

    Range("A" & lastrow, "K" & lastrow) = ""
    ThisWorkbook.Worksheets("汇总表").Range("A" & I + 2) = ThisWorkbook.Worksheets(I).Name
    ThisWorkbook.Worksheets(I).Range("d" & lastrow + 1).Copy Destination:=ThisWorkbook.Worksheets("汇总表").Range("B" & I + 2)
    ThisWorkbook.Worksheets(I).Range("e" & lastrow + 1).Copy Destination:=ThisWorkbook.Worksheets("汇总表").Range("c" & I + 2)
    ThisWorkbook.Worksheets(I).Range("f" & lastrow + 1).Copy Destination:=ThisWorkbook.Worksheets("汇总表").Range("d" & I + 2)

    Next I

    Application.GoTo Worksheets("汇总表").Range("A1"), Scroll:=True

End Sub

高个子

这个函数只能用宏表函数，用了宏表函数也要另存为xlsm格式，个人感觉还不如直接用VBA解决。。