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

yahavzarfati

New Member
Joined
Jun 17, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 
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 "

VBA Code:
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
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top