Private Sub Report_Click()
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim n As Long
Dim i As Long
Dim fRow As Long
Dim startDate As Date
Dim endDate As Date
Set wb = ActiveWorkbook
Set wsSrc = wb.Sheets("Faults")
Set wsSrc1 = wb.Sheets("Forced values")
Set wsSrc2 = wb.Sheets("Out of reserve")
Set wsSrc3 = wb.Sheets("Availability LOG")
Set wsSrc4 = wb.Sheets("Events ")
Set wsSrc5 = wb.Sheets("IEC Communication")
Set wsSrc6 = wb.Sheets("UNIT 1")
Set wsSrc7 = wb.Sheets("UNIT 2")
Set wsSrc8 = wb.Sheets("UNIT 1")
Set wsSrc9 = wb.Sheets("PTW")
Set wsDest = wb.Sheets("Report")
n = wsSrc.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
startDate = Application.InputBox("Enter the start date")
endDate = Application.InputBox("Enter the end date")
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc.Range("A" & i).Value >= startDate And wsSrc.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc1.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc1.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc1.Range("A" & i).Value >= startDate And wsSrc1.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc1.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc2.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc2.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc2.Range("A" & i).Value >= startDate And wsSrc2.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc2.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc3.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc3.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc3.Range("A" & i).Value >= startDate And wsSrc3.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc3.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc4.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc4.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc4.Range("A" & i).Value >= startDate And wsSrc4.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc4.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc5.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc5.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc5.Range("A" & i).Value >= startDate And wsSrc5.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc5.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc6.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc6.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc6.Range("A" & i).Value >= startDate And wsSrc6.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc6.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc7.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc7.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc7.Range("A" & i).Value >= startDate And wsSrc7.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc7.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc8.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc8.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc8.Range("A" & i).Value >= startDate And wsSrc8.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc8.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
n = wsSrc9.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc9.Range("A2").EntireRow.Copy wsDest.Cells(fRow, 1)
For i = 2 To n
If wsSrc9.Range("A" & i).Value >= startDate And wsSrc9.Range("A" & i).Value <= endDate Then
fRow = wsDest.Range("A:A").Find(what:="").Row
wsSrc9.Range("A" & i).EntireRow.Copy wsDest.Cells(fRow, 1)
End If
Next
End Sub