VBA nur einmal am Tag
chris58
Ich ersuche um Eure Hilfe. Ich habe dieses VBA das auch funktioniert. Die Daten werden mehrmals am Tag abgeglichen. Jedoch sollen die Daten nur einmal am Tag in das Tabellenblatt "Statistik" geschrieben werden. Was muß ich ändern, damit nur einmal geschrieben wird.
Danke für Eure Antwort
chris
Sub Protokoll()
Dim i As Long
Const NewConstSheet As String = "Statistik"
Dim bfound As Boolean
Dim sMerk As String
Dim sMaxZeile As Long
Dim TB As Worksheet
Application.ScreenUpdating = False
'Prüfen ob Tabelle NewConstSheet schon angelegt ist
For i = 1 To ActiveWorkbook.Sheets.Count
If ActiveWorkbook.Sheets(i).Name = NewConstSheet Then
bfound = True
Exit For
End If
Next i
'wenn nicht dann anlegen
If bfound = False Then
sMerk = ActiveWorkbook.ActiveSheet.Name
ActiveWorkbook.Sheets.Add after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveWorkbook.ActiveSheet.Name = NewConstSheet
ActiveWorkbook.Sheets(sMerk).Activate
End If
Set TB = ActiveWorkbook.Sheets(NewConstSheet)
'nächste leere Zeile ermitteln
sMaxZeile = TB.Cells(TB.Rows.Count, 1).End(xlUp).Row + 1
'Daten in neue Tabelle übertragen
TB.Cells(sMaxZeile, 1) = ActiveWorkbook.ActiveSheet.Range("D8")
TB.Cells(sMaxZeile, 2) = ActiveWorkbook.ActiveSheet.Range("B8")
TB.Cells(sMaxZeile, 3) = ActiveWorkbook.ActiveSheet.Range("B11")
TB.Cells(sMaxZeile, 4) = ActiveWorkbook.ActiveSheet.Range("B12")
TB.Cells(sMaxZeile, 5) = ActiveWorkbook.ActiveSheet.Range("C23")
TB.Cells(sMaxZeile, 6) = ActiveWorkbook.ActiveSheet.Range("C24")
TB.Cells(sMaxZeile, 7) = ActiveWorkbook.ActiveSheet.Range("B22")
TB.Cells(sMaxZeile, 8) = ActiveWorkbook.ActiveSheet.Range("C22")
TB.Cells(sMaxZeile, 9) = ActiveWorkbook.ActiveSheet.Range("K25")
TB.Cells(sMaxZeile, 10) = ActiveWorkbook.ActiveSheet.Range("K28")
Application.ScreenUpdating = True
End Sub