Autofilter and Copy Selected Columns to a new Sheet (in a different order)

    Hi


    I have a very large worksheet that I would like to apply filters to (using VBA) and copy selected columns to a new sheet - in a different order.


    Each time the report is run, I would like the previous content on the Report sheet to be cleared (leaving the headers in place)


    I have created a very simplified sample workbook (which should be uploaded).


    In the Criteria sheet, the two filters are selected from the dropdowns.


    The Data sheet contains the data I wold like to filter and then copy selected columns to the Report sheet.


    From looking at the Data & Report sheets, you will see that once the filters are applied, I only want to copy columns A, B, C, D, E and H. In the Report sheet, I need them to appear in the order A, C, D, E, H, B.


    This is a very simplified example, the 'live sheet' I am working with contains c. 150 columns and hundreds of rows.


    I have tried various combinations of Autofilter and Union, but I can't get it to work.


    Is anyone able to help please?


    Thanks


    Maid1812

    With Power Query, I was able to create the option for you to select the criteria and have the data updated. the file is attached for your review. Select your options from the drop downs and then click on the Command Button.


    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Hello MAID1812,


    Here's another VBA option:-


    I hope that this helps.


    Cheerio,

    vcoolio.

    Thanks everyone for your help, Much appreciated.


    I'm not familar with Power Query at all, so I'm sticking with the traditional route (although I'm clearly not great wityh that either!!). Apologies @alansidman.


    @gijsmo - I have taken the code you supplied (v2) incorporating @vcoolio's suggestions, and I've tweaked it to fit with my actual 'live' reporting needs. However, I cannot get it to function properly.


    See the attached spreadsheet with the code included and a couple of my comments.


    Sheets are:


    iGARApplications - the source data - cutdown version only two de-personalised rows included.

    Report Generation -criteria input. Only assigned code to the 'Platinum' button for now.

    Report - Platinum - Output

    Report - Platinum Saved (just a saved copy of the report column headers - to paste back in when I encounter errors

    List - a single named range for the report criteria dropdown.


    You will see from the code the input columns from the source and the order they need to be reported in.


    For the report output, I always need rows 1 - 3 to be there (including the read header row). At the moment these are being deleted (when I can get it to work).


    On line 39 of the code, when rSRC range is set to A1 I get runtime error 1004. I've managed to get the report to run setting this to A3 and A4, but whilst it selects the correct ID for the criteria, it only outputs c. 3 fields - and a strange one off to the right. The rest are blank.


    Any additional help you can give me to get this working would be appreciated. I've probably done something dim!


    Many thanks


    Maid1812

    The layout on this new report sample is somewhat different to the original sample provided so the code, whilst generic, was not going to work as is. With extra row headers above the main data and report fields and with the data grouping, the approach needs to be somewhat different.


    Taking the revised layout into account and with some interpretation of where I think you are heading with this, I've attached an updated version which I think now correctly creates the Platinum report. It should also work for Gold. I have intentionally not created a sheet for Silver or Bronze, just so you can see how the code works in that scenario. This also means that macros are now attached to each of the Report buttons on the Report Generation sheet.


    Test Reporting v2.xlsm

    @gijsmo - Thank you so much, that worked like a dream!!! If I could, I'd gladly buy you a celebratory beer (or beverage of choice!). Really appreciate your help :-).


    One question - and I don't want you to spend any time on this at all - is there a simple way to copy the cell's values (rather than the contents),i.e. at the moment, its copying over some conditional formatting and some formulas (In my 'live' worksheet, Owner and Manager name, for example are extracted by a formula which extracts the name from the email address).


    Its not a big deal - just wondered if there was a simple change that a novice, i.e. me, could make.


    Thanks again for your help. You're a star!!!


    Maid1812

    You are most welcome & a celebratory beer would be fantastic given our ongoing lockdowns in Australia !...so I'll just have to drink one in your honour ;)


    In terms of that change you require for your "live" sheet, you should be able to do this by changing the line of code in the Create_Report sub from:

    Code
    rRng.Copy Destination:=wTarg.Cells(RepStartRow + 1, aOut(k))

    to:

    Code
    rRng.Copy
wTarg.Cells(RepStartRow + 1, aOut(k)).PasteSpecial Paste:=xlPasteValues

    or if you want formatting as well as values, you could alternatively use:

    Code
    rRng.Copy
wTarg.Cells(RepStartRow + 1, aOut(k)).PasteSpecial Paste:=xlPasteValues
wTarg.Cells(RepStartRow + 1, aOut(k)).PasteSpecial Paste:=xlPasteFormats


    For completeness, I've updated the last copy with the above changes.

    Test Reporting v3.xlsm

    Hi @gijsmo


    Fantastic!! Thanks so much. You've been amazing.


    I didn't realise you were in Aus. I've been there several times to visit family - brother in Forster, nephew and niece in Sydney and another nephew in Adelaide. I love it and I'm overdue another visit - not sure that's happening any time soon :(


    I hope the Covid situation settles down there - although its nowhere near as widespread as here in the UK.


    Enjoy your cold one - I'll have one for you too.


    Thanks again for your help.


    Cheers


    David

    Thanks for the kind words, glad it worked for you.


    With family in Australia, particularly New South Wales, you're probably aware that covid is becoming as unmanageable here as other parts of the world...but still comparatively mild compared to UK numbers.


    I don't think we'll be travelling overseas any time soon, let alone within Aus at the moment.


    Stay safe in the meantime.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login