Macro to copy select rows to another worksheet and sort by month

Question

Does anyone have a macro that will take data from a compiled list, sort out a particular month, the paste only the sorted month into a new worksheet?

What I am setting up is a log where the samples are placed in the "Full List" by all employees and then when the boss wants to view only a certain months samples say January, he will be able to click the January button and it brings him to a worksheet which has copied only the "January" dated samples from the Full List worksheet into a new location for viewing. Since the data continually comes in it would need to sort and select new data each time.

Thanks for your help in advance!

Answer 1

I just made a mock workbook as you described, using the .advancedfilter function.

Only resource I used was this: http://www.vbaexpress.com/kb/getarticle.php?kb_id=567

You should be able to figure it out from that, and from the example spreadsheet you can download from the bottom. If you can't, you're in over your head I'm afraid :)

Answer 2

This can be done without VBA by creating a pivot table grouped by month. When you double click on the value for a particular month, a new sheet will automatically be created and populated with a filtered set of the data for that month.

• Select your data then click on 'Data - PivotTable and PivotChart Report'

• Click on the Next button, the Next button again and then the Finish button

• Drag your date field into the row fields

• Drag the employee name into the data item section

• Right click on the row fields containing the date and select 'Group and Show detail - Group'

• Set a start and end date and choose to group by months

Now double clicking on any amount in the data item area will create a new sheet showing the data that was used to generate the number.

As your data is continually changing, create a Dynamic Named Range using 'Insert - Name - Define' to use as the pivot table range. The formula for the Dynamic Named Range would look similar to this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A),3)

If you are using Excel 2007, then mark your data as a table (Ctrl-T) and use this as the basis for a pivot table to achieve the same effect.