Import and update automatically in a master sheet filtered data from daily sheets

[vladimiratanasiu]

Hello!

I have an annual workbook with monthly sheets that contain daily data of the financial transactions (see the link Billing documents.xlsx ). Each daily financial operation, depending on the payment term, is considered closed (rows colored in black), or still active (rows colored in red). I need a solution to import automatically all filtered active data (see example from the attached file) in the master sheet SAP Document Export, and remove them similarly from that location at the moment they become closed and black in the daily files. I need also the updated data to auto arrange in the master sheet, based on the Date sequence and, if possible, on the ID item, too. Thank you!

 

[mumps]

remove them similarly from that location at the moment they become closed and black in the daily files

When you say "remove", do you mean permanently delete the black rows or simply hide them by filtering them so they are hidden but not deleted?

 

[vladimiratanasiu]

When you say "remove", do you mean permanently delete the black rows or simply hide them by filtering them so they are hidden but not deleted?

I need them to be permanently deleted from the master sheet, keeping available at the same time their sources from the monthly files .

 

[mumps]

I'm not sure if I understood correctly but try this macro. It assumes that sheet "SAP Document Export" is the first sheet and that there are no other sheets except for the monthly sheets.

Sub UpdateData()
    Application.ScreenUpdating = False
    Dim x As Long, desWS As Worksheet
    Set desWS = Sheets("SAP Document Export")
    For x = 2 To Sheets.Count
        With Sheets(x)
            .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
            .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            .Range("A1").AutoFilter
        End With
    Next x
    Application.ScreenUpdating = False
End Sub

 

[vladimiratanasiu]

I'm not sure if I understood correctly but try this macro. It assumes that sheet "SAP Document Export" is the first sheet and that there are no other sheets except for the monthly sheets.

Sub UpdateData()
    Application.ScreenUpdating = False
    Dim x As Long, desWS As Worksheet
    Set desWS = Sheets("SAP Document Export")
    For x = 2 To Sheets.Count
        With Sheets(x)
            .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
            .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            .Range("A1").AutoFilter
        End With
    Next x
    Application.ScreenUpdating = False
End Sub

Thank you for your quick response. I applied your macro and it is runs well with regard to my needs. However, ther is a small issue: when I make a change in one day's content and try to run the macro again, the previous list from master sheet is updated by adding a new whole list to the previous one. I wish the old list to be updated taking into account only individual changes, not the entire content. Thank you!

 

[mumps]

Try:

Sub UpdateData()
    Application.ScreenUpdating = False
    Dim x As Long, desWS As Worksheet, ID As Range, y As Variant
    Set desWS = Sheets("SAP Document Export")
    For x = 2 To Sheets.Count
        With Sheets(x)
            .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
            For Each ID In .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
                y = Application.Match(ID, desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)), 0)
                If IsError(y) Then
                    .Range("A" & ID.Row).Resize(, 6).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                End If
            Next ID
            .Range("A1").AutoFilter
        End With
    Next x
    Application.ScreenUpdating = False
End Sub