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 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 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. Can anyone help? Must confess I'm hoping someone can do it for me then I can study it!

 

[Ajwilltravel]

Thanks so much !!

 

[vcoolio]

Hello AJ,

I haven't been able to access the workbook in your link, however, based on what you have said in previous posts, try the following:-

Option Explicit
Sub Test()

        Dim wsS As Worksheet, wsAR As Worksheet, ws As Worksheet
        Dim ar As Variant, i As Long, lr As Long
        Set wsS = Sheets("Summary")
        ar = Array("RMARN", "WTP")
   
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
       
wsS.[A2].CurrentRegion.Offset(1).Clear

        For i = 0 To UBound(ar)
            Set wsAR = Sheets(ar(i))
            lr = wsAR.Range("A" & Rows.Count).End(xlUp).Row
                 With wsAR.Range("F5:F" & lr)
                            .AutoFilter 1, "<>" & "Finished"
                        With .Resize(.Rows.Count - 1)
                               Union(.Columns("B:F"), .Columns("O:P"), .Columns("U")).Offset(1, -5).Copy
                               wsS.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                               .AutoFilter
                               wsS.Columns.AutoFit
                        End With
               End With
        Next i
   
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

P.S. Just managed to access your workbook. I've tweaked the code a little bit. It should now do the task for you.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Copy certain columns of data from 2 worksheets that are auto-filtered to 1 summary wo
and here Copy Paste as Values certain columns from Autofiltered data in 2 worksheets to 1 Summary worksheet - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Ajwilltravel]

Hello AJ,

I haven't been able to access the workbook in your link, however, based on what you have said in previous posts, try the following:-

Option Explicit
Sub Test()

        Dim wsS As Worksheet, wsAR As Worksheet, ws As Worksheet
        Dim ar As Variant, i As Long, lr As Long
        Set wsS = Sheets("Summary")
        ar = Array("RMARN", "WTP")
  
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
      
wsS.[A2].CurrentRegion.Offset(1).Clear

        For i = 0 To UBound(ar)
            Set wsAR = Sheets(ar(i))
            lr = wsAR.Range("A" & Rows.Count).End(xlUp).Row
                 With wsAR.Range("F5:F" & lr)
                            .AutoFilter 1, "<>" & "Finished"
                        With .Resize(.Rows.Count - 1)
                               Union(.Columns("B:F"), .Columns("O:P"), .Columns("U")).Offset(1, -5).Copy
                               wsS.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                               .AutoFilter
                               wsS.Columns.AutoFit
                        End With
               End With
        Next i
  
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

P.S. Just managed to access your workbook. I've tweaked the code a little bit. It should now do the task for you.

Thank you everything is working except it also copied all the "Finished" items... so I only want to copy items to the summary sheet that are not "Finished". So close though!! How might I tweak code above to exclude "Finished"? then I am done !!! Thanks again much appreciated!

 

[Ajwilltravel]

Thank you everything is working except it also copied all the "Finished" items... so I only want to copy items to the summary sheet that are not "Finished". So close though!! How might I tweak code above to exclude "Finished"? then I am done !!! Thanks again much appreciated!

 

[Ajwilltravel]

Ok I worked it out:


.AutoFilter 1, "<>Finished"


.. but for some reason it is still coping in the very 1st "Finished" row on the RMARN tab, but none of the other "Finished" rows (which is what I want). Not sure why that 1st row is still coming through though... thanks again

 

[Ajwilltravel]

Sorry to be a pain but can you also tell me how to exclude anything with nil value copying over to summary - so I want to exclude anything that does not have either "Finished" or nil value in column F (in the 2 sheets - so only those rows with values. Something like..


.AutoFilter 1, "<>Finished", "<>0"

Thanks again!

 

[Ajwilltravel]

Tried this but no luck

.AutoFilter 1, Criteria1:="<>Finished", Operator:=xlOr, Criteria2:="=0.00"

 

[Ajwilltravel]

But this worked !!

.AutoFilter 1, Criteria1:="<>Finished", Operator:=xlAnd, Criteria2:="<>0.00"

thanks so much !!!

 

[Ajwilltravel]

Sorry one last question. When I run macro "Copy to Summary" - is it possible to clear and replace what was already in "Summary" tab - since the last time i ran the macro? Cheers