Copy Rows from multiple Worksheets to another sheet based on date cell

[yahavzarfati]

Hi im new to VBA and can not get any progress on this one

i have 10 source worksheets and i need to copy all rows from the last 48 hours to target worksheet thet called "report sheet"
in all source sheet the date is on cell "a" and start from "a3", but the length of the row is not same on all source sheets
tha name of sorce sheet for example:
"unit1"
"unit 2"
"Events"

pleas halp me to start this project

 

[yahavzarfati]

I found this code it seems to me that if I can make it work for one sheet it will be a good start
i get a compile error on this line
" fRow = wsDest.Range("A:A").Find(what:="").Row "

Dim wb As Workbook
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("Source Sheet")
Set wsDest = wb.Sheets("Dest Sheet")

n = wsSrc.Range("E:E").Find(what:="*", searchdirection:=xlPrevious).Row
startDate = Application.InputBox("Enter the start date")
endDate = Application.InputBox("Enter the end date")

For i = 2 To n

    If wsSrc.Range("BB" & i).Value >= startDate And wsSrc.Range("BB" & i).Value <= endDate Then

        fRow = wsDest.Range("A:A").Find(what:="").Row
        wsSrc.Range("BB" & i).EntireRow.Copy wsDest.Cells(fRow, 1)

    End If

Next

 

[yahavzarfati]

update:
I was able to finally get the code to work
But it's poorly written and the code is long and I think a lot of lines in it are unnecessary

1. If someone has a tip on how to write it in a more sophisticated way that would be great
2. I need to add row before each one of the sheet copied to "Report" sheet with the original name of the sheet

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