vba 宏将选择的行复制到另一个工作表并按月排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1067294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to copy select rows to another worksheet and sort by month
提问by Robert Mearns
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!
提前感谢您的帮助！
回答by Izzy
I just made a mock workbook as you described, using the .advancedfilter function.
我刚刚按照您的描述制作了一个模拟工作簿，使用 .advancedfilter 函数。
Only resource I used was this: http://www.vbaexpress.com/kb/getarticle.php?kb_id=567
我使用的唯一资源是：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 :)
您应该能够从中弄清楚，并且您可以从底部下载示例电子表格。如果你做不到，恐怕你就疯了:)
回答by Robert Mearns
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.
这可以在没有 VBA 的情况下通过创建按月分组的数据透视表来完成。当您双击特定月份的值时，将自动创建一个新工作表并填充该月份的一组过滤数据。
Select your data then click on 'Data - PivotTable and PivotChart Report'
Click on the Nextbutton, the Nextbutton again and then the Finishbutton
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,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.
如果您使用的是 Excel 2007，则将您的数据标记为表格 ( Ctrl-T) 并将其用作数据透视表的基础以实现相同的效果。
