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!

 

[vcoolio]

Hello AJ,

A sample of your workbook would come in handy. However, see if the following at least steers you in the right direction:

Option Explicit
Sub Test()

        Dim ws As Worksheet, wsS As Worksheet
        Set wsS = Sheets("Summary")

Application.ScreenUpdating = False

        wsS.[A2].CurrentRegion.Offset(1).Clear
        
        For Each ws In Worksheets
                If ws.Name <> "Summary" Then
                        With ws.[A1].CurrentRegion
                        .AutoFilter 6, "Finished"
                                Union(.Columns("B:F"), .Columns("O:P"), .Columns("U")).Offset(1).Copy
                                wsS.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                                .AutoFilter
                        End With
                End If
        Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've attached a mock-up of how this could work here. Click on the blue button to see it work. Hopefully, the mock-up is close to how your workbook is set out.

I hope that this helps.

Cheerio,
vcoolio.

 

[Ajwilltravel]

Thanks so much I’ll try this in the morning. I have the workbook but I could find anywhere to upload it. I’m new to this forum. Do you have to do something to enable uploading of files? If so, once I know how to I can upload the file & it will all make sense ! Thanks again ??

 

[vcoolio]

Hello AJ,

You can upload "mini sheets" using the forum's XL2BB set up. Look here.
You could also upload your workbook by using a file sharing site such as WeTransfer or Drop Box. These sites allow you to freely upload files but there is usually a time limit on how long they can be accessed (usually 7 - 30 days). If you use a file sharing site and your data is sensitive, then please use dummy data.
The sample I supplied in my last post is through WeTransfer but will only be available for a week. You can download and save it of course.

Cheerio,
vcoolio.

 

[Ajwilltravel]

Thanks so much Vcoolio but I can't open your link unfort im getting msg below. The mini sheets thing didn't work for me either - could not install it (perhaps something to do with my workplace firewalls?). I will use WeTransfer or Drop Box to send it - not sure how to use these but will work it out! Who am I sending it to exactly? Thanks so much

 

[Ajwilltravel]

I tried your code above and am getting an out of range error. The columns are all correct. Amazing you did this without seeing my workbook!

I think it might be this part of the code:

If ws.Name <> "Summary" Then

As the original workbook has other sheets (which I removed before uploading the file here - as that data is sensitive) - I think it's reading all those sheets too.

How can I change

If ws.Name <> "Summary" Then to If (names of the 2 sheets "WTP" and "RMARN" only?) - as it's only those 2 sheets I need to copy from (they are the autofiltered sheets in - in my example workbook).

thanks again!

 

[Ajwilltravel]

OK yes my company PC is blocking use of WeTransfer. I will try to send my file via this from home tonight - and also open the file you transferred above..

 

[Ajwilltravel]

Ok worked out how to link ! Here it is. And I’ve added your code to it… see summary sheet for result. Thank you again you’re a life saver ! Mr Excel rev 2 TEST ACCRUALS SHEET WITH MACRO.xlsm

 

[Ajwilltravel]

sorry meant to add, it's not quite working. if you wouldn't mind taking a look? thanks again

 

[vcoolio]

Hello AJ,

I'll have a look at it tomorrow for you.

Cheerio,
vcoolio.