Compile Several Worksheets of Data based on Month

I have a workbook that has several sheets of data...all same columns, different number of rows on each. I need to compile data from all sheets based on the month the data closed. I have attached an example worksheet. 30 day would be gathering data from current month, 60 days would be future month (i.e. October), and the same with 90 day (November).
I know I can do a v lookup to return the data in the columns however I would have to copy the "month" column to column A and because of another macro I have in the sheet, when the employee adds a new row, it doesn't copy the formula of the previous row which would bring the data over from the "month" column.
Is there another way to gather this data?

So looking at your workbook, i have the following questions:

1. You mean to say that the data from the Scott, Tim and Paul tabs needs to be segregated into the 3 tabs - 30 day, 60 day and 90 day based on the month when it was closed?

2. Only the rows that have status as "Closed" should be copied or all rows?

All data rows from Scott, Tim and Paul needs to pull into the three tabs respectively depending on month it was closed - that is correct. All data should be copied, not just the rows that show 'closed'.

Thanks

Will you always have 3 separate tabs for the people - Scott, Tim and Paul? Will there be more such tabs?

There will be more tabs as people are added. We can definitely change the month to a number, that will be easy. Thank you so much for your help.

I have changed the month to a number and the code is as below:

Sub compile_data()

Worksheets(1).Select

For i = 1 To Worksheets.Count - 4

    If Worksheets(i).Name <> "30 Day" Or Worksheets(i).Name <> "60 day" Or Worksheets(i).Name <> "90 day" Or Worksheets(i).Name <> "Extra" Then
        
        Worksheets(i).Select
        lastrow = Worksheets(i).Range("A1").End(xlDown).Row
        
        For j = 2 To lastrow
        
        Worksheets(i).Rows(j & ":" & j).Copy
        
            If Worksheets(i).Range("E" & j).Value = Month(Date) Then
            
                Worksheets("30 day").Select
            
            ElseIf Worksheets(i).Range("E" & j).Value = Month(Date) + 1 Then
                
                Worksheets("60 day").Select
            
            ElseIf Worksheets(i).Range("E" & j).Value = Month(Date) + 2 Then
            
                Worksheets("90 day").Select
            
            Else
            
                Worksheets("Extra").Select
            
            End If
           
        If Range("A2") = "" Then
        
            lrow = 1
        
        Else
        
            lrow = Range("A1").End(xlDown).Row
            
        End If
        
        Range("A" & lrow + 1).Select
        ActiveSheet.Paste
             
        Next
    
    End If
    
Next

End Sub

I found some entries for September which were not fitting anywhere so i moved them to the "Extra" Tab. If any rows don't meet the criteria, they will go there. That way you can identify if any data is out of place.

Thanks so much for your help. I pasted the code in the worksheets and when I run the macro it gives me a "400" error? I am not sure what that means.

You need to ensure that you paste the code in the Module. Also ensure that you have the "Extra" tab present.

Also, any hidden worksheet in your workbook?

Thanks. I will make sure I paste it into the MOdule. There is one hidden worksheet for some dropdown menus we have. Will that make a difference?

It shouldnt. If it does, let me know what error you get.

Still getting the "400" error. I wonder if it is because there is too much data. In my actual spreadsheet there are 8 sheets that will feed the 30 / 60 / 90 day summary tabs. I did change the sheet count in the code to match 8 sheets however I am still getting the error. I pasted it into the module on the sheet however it still gives me the error.

Thanks...that worked. One last question - it is bringing over the "total" rows from all 8 sheets. Is there a line we could add that if the first cell on the row = total, not to include that row?

This is wonderful - I cannot tell you how much I appreciate your help with this.

Try this out and let me know :

Sub compile_data()

Worksheets(1).Select

For i = 1 To Worksheets.Count - 4

    If Worksheets(i).Name <> "30 Day" Or Worksheets(i).Name <> "60 day" Or Worksheets(i).Name <> "90 day" Or Worksheets(i).Name <> "Extra" Then
        
        Worksheets(i).Select
        lastrow = Worksheets(i).Range("A1").End(xlDown).Row
        
        For j = 2 To lastrow
        
        If Range("A" & j).Value <> "Total" Then
        
            Worksheets(i).Rows(j & ":" & j).Copy
        
                If Worksheets(i).Range("E" & j).Value = Month(Date) Then
            
                    Worksheets("30 day").Select
            
                ElseIf Worksheets(i).Range("E" & j).Value = Month(Date) + 1 Then
                
                    Worksheets("60 day").Select
            
                ElseIf Worksheets(i).Range("E" & j).Value = Month(Date) + 2 Then
            
                    Worksheets("90 day").Select
            
                Else
            
                    Worksheets("Extra").Select
            
                End If
            
           End If
           
        If Range("A2") = "" Then
        
            lrow = 1
        
        Else
        
            lrow = Range("A1").End(xlDown).Row
            
        End If
        
        Range("A" & lrow + 1).Select
        ActiveSheet.Paste
             
        Next
    
    End If
    
Next

End Sub

Thanks. That worked with a few exceptions - it now only pulls data into the "extra" tab - the 30 day/60 day/90 day doesn't get any data. It is pulling everything over but continues to pull over the total column from 3 sheets - haven't figured out which sheets yet.

Is the total field just below the data or is there a row in between?

Please post the latest sample workbook with the 8 sheets and i can try something out for you.

There is a row in between the total row and the last row of data. I have attached the updated sample workbook with the two macros included - one in the module and one in the workbook (that one adds the extra row between data and total row on each sheet).
Thank you so much for your help with this.

I have checked the macro. It works now. The numbers stored in the Close month column were stored as text so it was not considering it. You need to format it as numbers. Then it works.

Also i have observed that you have some other code in the "This Workbook" section. My code doesnt get affected, but i have observed that it does go to that code and then come back. This is just for your information.

I have just added a few extra lines - here is the updated code -

Sub compile_data()

Worksheets(1).Select

For i = 1 To Worksheets.Count - 4

    If Worksheets(i).Name <> "30 Day" Or Worksheets(i).Name <> "60 day" Or Worksheets(i).Name <> "90 day" Or Worksheets(i).Name <> "Extra" Then
        
        Worksheets(i).Select
        lastrow = Worksheets(i).Range("A1").End(xlDown).Row
        
        For j = 2 To lastrow
        
        If Range("A" & j).Value <> "Total" Then
        
            Worksheets(i).Rows(j & ":" & j).Copy
        
                If Worksheets(i).Range("E" & j).Value = Month(Date) Then
                    Worksheets("30 day").Select
                ElseIf Worksheets(i).Range("E" & j).Value = Month(Date) + 1 Then
                    Worksheets("60 day").Select
                ElseIf Worksheets(i).Range("E" & j).Value = Month(Date) + 2 Then
                    Worksheets("90 day").Select
                Else
                    Worksheets("Extra").Select
                End If
            
           End If
           
        If Range("A2") = "" Then
            lrow = 1
        ElseIf Range("A3") = "" Then
            lrow = 2
        Else: lrow = Range("A1").End(xlDown).Row
        End If
        
        Range("A" & lrow + 1).Select
        ActiveSheet.Paste
             
        Next
    
    End If
    
Next

End Sub