AW: vba-Liste von Daten aus Arbeitsblättern
16.02.2019 17:57:04
Daten
Hallo Fred,
Modul Modul1
Option Explicit
Sub collectData()
Dim objWS As Worksheet, rng As Range, lngLast As Long
Dim varOut() As Variant, lngI As Long, lngR As Long, lngSheet As Long
Dim datSearch As Date
datSearch = Sheets("Stichworte").Range("A1")
For lngSheet = 1 To 20
If SheetExist(CStr(lngSheet)) Then
With Sheets(CStr(lngSheet))
lngLast = Application.Max(1, .Cells(.Rows.Count, 2).End(xlUp).Row)
For lngR = 1 To lngLast
If IsDate(.Cells(lngR, 2)) And Not .Rows(lngR).Hidden Then
If .Cells(lngR, 2) >= datSearch Then
Redim Preserve varOut(lngI)
varOut(lngI) = Array(.Cells(lngR, 48).Value, lngSheet)
lngI = lngI + 1
End If
End If
Next
End With
End If
Next
If lngI > 0 Then
With Sheets("Stichworte")
.Range("A2:B" & .Rows.Count) = ""
.Range("A2").Resize(lngI, 2) = Application.Transpose(Application.Transpose(varOut))
End With
End If
End Sub
Private Function SheetExist(ByVal sheetName As String, Optional Wb As Workbook, Optional ByVal byCodeName As Boolean = False) As Boolean
Dim wks As Object
On Error GoTo ERRORHANDLER
If Wb Is Nothing Then Set Wb = ThisWorkbook
For Each wks In Wb.Sheets
If byCodeName Then
If LCase(wks.CodeName) = LCase(sheetName) Then SheetExist = True: Exit Function
Else
If LCase(wks.Name) = LCase(sheetName) Then SheetExist = True: Exit Function
End If
Next
ERRORHANDLER:
SheetExist = False
End Function
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0