Summe - Tabellenblätter - Zelle - Zellen...

Frage: Für jeden Tag eines Jahres gibt es ein Tabellenblatt. Zusätzlich noch ein Summentabellenblatt. In diesem möchte ich die Summe von einer oder zwei Zellen aus allen Tabellenblättern. Die Tabellenblätter haben das Datum als Namen. Deshalb möchte ich auch über die Eingabe eines Start- bzw. Enddatums die Summe dieser beiden Zellen wissen. Ein Tabellenblatt für jeden Tag des Jahres erstellen - siehe "http://vbanet.blogspot.de/2012/10/ein-tabellenblatt-fur-jeden-tag-des.html".

For each day of the year, there is a worksheet. Additionally a total spreadsheet. In this I would like the sum of one or two cells from all worksheets. The spreadsheets have the date as the name. So I want to know by entering a start and end date, the sum of these two cells. A worksheet for each day of the year to create - see "http://vbanet.blogspot.de/2012/10/ein-tabellenblatt-fur-jeden-tag-des.html".

Hier noch eine Beispieldatei / Here's a sample file:
Summe - Tabellenblätter - Zelle - Zellen...[XLS 900 KB]

Option Explicit
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main()
    ThisWorkbook.Worksheets("Total").Range("A2").Formula = _
        "=SUM('01.01.2013:31.12.2013'!B4)"
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_1 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_1()
    ThisWorkbook.Worksheets("Total").Range("B2").Formula = _
        "=SUM('01.01.2013:31.12.2013'!C4)"
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_2 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_2()
    ThisWorkbook.Worksheets("Total").Range("C2").Formula = _
        "=SUM('01.01.2013:31.12.2013'!B4:C4)"
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_3 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_3()
    With ThisWorkbook.Worksheets("Total").Range("A2")
        .Formula = "=SUM('01.01.2013:31.12.2013'!B4)"
        .Value = .Value
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_4 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_4()
    With ThisWorkbook.Worksheets("Total").Range("B2")
        .Formula = "=SUM('01.01.2013:31.12.2013'!C4)"
        .Value = .Value
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_5 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_5()
    With ThisWorkbook.Worksheets("Total").Range("C2")
        .Formula = "=SUM('01.01.2013:31.12.2013'!B4:C4)"
        .Value = .Value
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_6 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_6()
    With ThisWorkbook.Worksheets("Total")
        .Range("A2").Formula = _
            "=SUM('" & .Range("B10").Text & ":" & .Range("C10").Text & "'!B4)"
        .Range("A2").Value = .Range("A2").Value
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_7 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_7()
    With ThisWorkbook.Worksheets("Total")
        .Range("B2").Formula = _
            "=SUM('" & .Range("B10").Text & ":" & .Range("C10").Text & "'!C4)"
        .Range("B2").Value = .Range("B2").Value
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Modul1 
' Procedure : Main_8 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 26.02.2013 
' Purpose   : Sum certain worksheets certain areas... 
'-------------------------------------------------------------------------- 
Sub Main_8()
    With ThisWorkbook.Worksheets("Total")
        .Range("C2").Formula = _
            "=SUM('" & .Range("B10").Text & ":" & .Range("C10").Text & "'!B4:C4)"
        .Range("C2").Value = .Range("C2").Value
    End With
End Sub

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...