Copy certain columns of data from 2 worksheets that are auto-filtered to 1 summary worksheet as values

[ajwilltravel]

Hi everyone, I am a macro beginner. I have two worksheets with source data (layout of each worksheet is the same but number of rows on each sheet will continue to grow). In these 2 sheets 'WTP' and 'RMARN' I've managed to create a macro to autofilter (Filter on column F - Filter out any rows that are "Finished") - macro button top left). This column F is also a formula. My other macro is simply to clear the filter. What I need is a macro to copy certain columns from both of these source data sheets (those rows that are not “Funished” so the filtered lists) and copy paste as values in the 'Summary' sheet. I need to do this monthly so each time I run it I need to clear what is in the summary sheet from last time and run the macro again (replace what was already there). The columns I need to copy from the two source sheets are shown in the summary tab in attached - B C D E F O P & U. But I don't want the column headers to come through (I’ve only shown them in this workbook as a guide - so no column headers just the data) to the summary sheet when I run the macro. I've been on internet all day trying to build this macro & have failed. Also tried recording then editing it but got completely lost. There are broken formulas in this workbook pls disregard them. I do need those columns I just had to delete their source as info is confidential. Can anyone help? Must confess I'm hoping someone can do it for me then I can study it!

 

[Marc L]

Hi, according to your attachment a VBA demonstration for a starting point :​

Sub Demo1()
    Dim S&
        Sheet8.UsedRange.Clear
    For S = Sheet8.Index + 1 To Sheets.Count
        With Sheets(S)
            If .FilterMode Then .Range(Replace(Replace("B#:F¤,O#:P¤,U#:U¤", "#", .AutoFilter.Range.Row + 1), _
                     "¤", .AutoFilter.Range(1).End(xlDown).Row)).Copy Sheet8.Cells(Rows.Count, 1).End(xlUp)(2)
        End With
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[ajwilltravel]

Hi thanks for this but I don't think it's actually doing what I need. I can't see the reference the 2 sheets "RMARN" & "WTP". I will have other sheets in the workbook so I need to specifically reference those 2 sheets only (where the data is being pulled from). Sorry if I didn't explain it well previously :/

 

[ajwilltravel]

I just tired to run it and it didn't work. Nothing actually happened

 

[Marc L]

As it works as expected on my side with your attachment according to your explanation :​

so the filtered lists

 

[ocsnadeem]

Filter and copy date to other sheet with clear exciting date
>>> use code - tags <<<

Sub Missing()
Dim LR As Long
Dim S&
LR = Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Working_Sheet")

       .Range("U2:U" & LR).AutoFilter Field:=21, Criteria1:="Missing In", _
    Operator:=xlOr, Criteria2:="<>"
    .Range("V2:V" & LR).AutoFilter Field:=22, Criteria1:="Missing Out"
   
    With Sheets("Missing_IN&Out")
 .Rows(2 & ":" & .Rows.Count).ClearContents
        For S = Sheet5.Index - 1 To Sheets.Count
        With Sheets(S)
            If .FilterMode Then .Range(Replace(Replace("A#:E¤,G#:G¤,H#:H¤,J#:J¤,L#:L¤,M#:O¤,U#:W¤", "#", .AutoFilter.Range.Row + 1), _
                     "¤", .AutoFilter.Range(1).End(xlDown).Row)).Copy Sheet5.Cells(Rows.Count, 1).End(xlUp)(2)
        End With
       
    Next
End With
End With
 End Sub

 

[Marc L]

A bit late, just 18 months after ‼​

​