Copy Paste as Values certain columns from Autofiltered data in 2 worksheets to 1 Summary worksheet

  • crossposted:…ksheet-as-values.1200086/

    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!

  • There's problems with your worksheets themselves. You have multiple formulas that aren't used, this is not good, every time your workbook calcualtes then all those unused formulas need to calculate, potentially slowing your workbook.

    Your summarised data has two headers, again this is not a good idea.

    How do you determine whether the Finished items have previously been copies or will they be deleted when the code runs?

  • Hi those formulas are being used I just had to delete their source data sheets to upload this workbook to this forum as that data is confidential. Please disregard those columns. But they do have to stay where they are. Those columns do not need to copied in to the summary tab. Regarding the headers in summary tab I only put them there as a guide. I do not want any headers to be copied in to the summary tab (after the macro runs). Only the data in the columns I marked up & mentioned in my original query. Thanks…

  • Sorry Re your question How do you determine whether the Finished items have previously been copies or will they be deleted when the code runs? anything finished will not be copied. That’s the macro I need - to copy only what isn’t finished from the 2 sheets to the summary tab. Does that make sense? Thanks again

  • Is this doing what you want.

    I've assigned the new macro to your button, you don't need the clear filter button because the new code does that

  • Roy firstly thanks so much for this!! Yes what is on the summary sheet is exactly what I need. However, I do need the WTP & RMARN sheets to have their own macros & macros buttons - so I can filter then unfilter finished at any time (toggle between the 2). The reason is I use those sheets during the month for analysis - and I need to see both finished unfinished constantly. The summary sheet macro only needs to be run once a month - it's the final result of my analysis in the other 2 sheets. So i wanted the summary sheet to have it's own button ("copy to summary") and it's own macro. And I want the macros on the buttons on the other sheets to remain as they were. Sorry I didn't explain all this properly! I'm trying to fix it here but not having much luck!

  • I've added another macro that will run on the individual sheets, assign this one to the buttons on each sheet.

    The original one assign to the button on the summary sheet.

  • rory

    Added the Label Cross Post
  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!

    Read this to understand why we ask you to do this

    Excelguru Help Site - A message to forum cross posters

Participate now!

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