移动的基础上的信息在一列中的数据,然后通过数据在另一个排序 [英] Moving data based on information in one column and then sorting by data in another

查看:382
本文介绍了移动的基础上的信息在一列中的数据,然后通过数据在另一个排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

确定,所以最好的办法来解释这一点,我有一个每月生产A S preadsheet。它需要被自动化。从这个被运到Excel的报告,我需要从项目数列取中的所有数据,并将其移动到另一个工作簿。在这个工作簿中的工作表是2012年和2013年,我需要开始300,然后400,然后500等搬进新的工作簿,然后2012年项目进入一个工作和2013年到另一个所有的项目。我怎么会去自动执行这样的事情?

Ok so best way to explain this, I have a spreadsheet that is produced monthly. It needs to be automated. From the report that gets shipped into excel I need to take all the data from the project number column and move it to another workbook. In this workbook are worksheets for 2012 and 2013. I need all of the projects starting 300, then 400, then 500 etc to move into the new workbook and then the 2012 projects to go into one worksheet and 2013 into another. How would I go about automating such a thing?

感谢您的帮助。

300873 公司名称的2012年10月9日结果
300874的企业名称的2012年10月9日结果
412774的企业名称的30-01-12结果
412972的企业名称的23-05-12结果
412986的企业名称的17-07-12结果
413001的企业名称的13年8月6日结果
413048的企业名称的14-08-12结果
413049的企业名称的14-08-12结果
413062的企业名称的20-08-13结果
413068的企业名称的23-08-12结果
413169的企业名称的21-09-13结果
510003的企业名称的27-12-12结果
600161的企业名称的28-02-12

300873 Company Name 10/09/2012
300874 Company Name 10/09/2012
412774 Company Name 30-01-12
412972 Company Name 23-05-12
412986 Company Name 17-07-12
413001 Company Name 08/06/13
413048 Company Name 14-08-12
413049 Company Name 14-08-12
413062 Company Name 20-08-13
413068 Company Name 23-08-12
413169 Company Name 21-09-13
510003 Company Name 27-12-12
600161 Company Name 28-02-12

所以这些我想所有2012年项目进入一片和所有其他人进入一个2013片在sperate工作簿中,我还需要300 ---项目走成S数字300的下方preadsheet并填写行,因此这可能包括添加行。

So with these I would want all of the projects with 2012 to go into one sheet and all the others to go into a 2013 sheet in a sperate workbook, I would also need the 300--- projects to go into the spreadsheet and fill rows underneath the number 300 so this might include adding rows.

推荐答案

您可以轻松地设计这个pretty宏。我要给你一些工具来使用,给它一个尝试,什么时候以及是否有你正在运行到回来的东西,发表您的code,并要求在这个问题上,你已经做了更多的问题我会帮你的code沿着这样你就可以学习。

you can design a macro for this pretty easily. I'm going to give you some tools to use, give it a try, when and if there is something you're running into come back, post your code, and ask more questions on this question you've already made and I'll help you along with the code so you can learn.

我需要从项目数列取中的所有数据,并将其移动到另一个工作簿。

I need to take all the data from the project number column and move it to another workbook.

首先,code从您的宏打开文件:

ChDir "[pathway to the folder]"       'loads file

Workbooks.Open Filename:= "[pathwayToFolder including filename]"

Workbooks("[workbook name]").Activate

别的东西,是很有用的是专指不同工作表的能力和范围,而不实际需要选择或去那些表。这code为例,具体写在一个特定的工作簿中的东西,片材和范围,而要他们

Something else that is useful is the ability to specifically refer to different sheets and ranges without actually have to select or go to those sheets. This code, for example, writes something specific in a specific workbook, sheet, and range without going to them

Workbooks("Book1").Sheets("Sheet1").Range("A5") = "Test"


二,复制和粘贴:

这code将所有的细胞和它们粘贴所有其他表单上,可以很明显的复制和粘贴更具体的范围(谷歌,如果你需要)

This code copies all cells and pastes them all on another sheet, you can obviously copy and paste more specific ranges (google it if you need)

Cells.Copy
Sheets("[sheetname]").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste


三,确定年份:

如果该日期在列C我只想做

If the date is in Column "C" I would just do this

Dim i As Integer, rowNumber As Integer
i = 1         'whatever row your data begins on
rowNumber = ActiveSheet.UsedRange.Rows.Count      'gets used number of rows

Do              'Loops through all data on the sheet

   If Right(Range("C" & i), 2) = "12" Then    'checks the last 2 characters in date column

      'copy data from that row and paste it wherever applicable   

   ElseIf Right(Range("C" & i), 2) = "13" Then  'checks for 2013

      'move 2013 data wherever applicable

   End If

   i = i + 1
Loop Until i = rowNumber


尝试启动把这个code连成的东西,回到我们这个问题。


Try to start putting this code together into something and get back to us on this question.

此外,在未来试着问之前做一些研究和工作在这个的,这个问题实际上是多个问题十分广阔,使得它很难回答。这就是为什么有人downvoted它。

Also, in the future try to do some of the research and work on this before asking, the question is actually multiple questions and very broad, making it hard to answer. That is why someone downvoted it.

希望这有助于!

这篇关于移动的基础上的信息在一列中的数据,然后通过数据在另一个排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆