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

.NET Framework IT屋
问 题

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?

Thanks for any help

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

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.

解决方案

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.


First, code for Opening a file from your macro:

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

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

Workbooks("[workbook name]").Activate

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"

Second, Copying and Pasting:

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

Third, Determining the Year:

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

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

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.

Hope this helps!

本文地址:IT屋 » Moving data based on information in one column and then sorting by data in another

问 题

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

感谢您的帮助。

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

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


解决方案

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


  

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



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

  CHDIR“[路径到文件夹]”'加载文件Workbooks.Open名:=“[pathwayToFolder包括文件名]”工作簿(“[工作簿名称]”)。激活

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

 工作簿(“第一册”)。表(“工作表Sheet1”)。范围(“A5”)=“测试”

二,复制和粘贴:

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

  Cells.Copy
表(“[SHEETNAME]”)。选择
ActiveSheet.Range(“A1”)。选择
ActiveSheet.Paste

三,确定年份:

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

 暗淡我作为整数,ROWNUMBER作为整数
I = 1'的任何行数据上开始
ROWNUMBER = ActiveSheet.UsedRange.Rows.Count'获得行的号码做'遍历表上的所有数据   如果右(范围(“C”和放大器;我),2)=“12”然后'检查日期列的最后2个字符      “从该行的数据复制并粘贴(如适用)   elseif的权利(范围(“C”和放大器;我),2)=“13”然后“检查2013      “移动数据2013适用情况   万一   I = I + 1
循环,直到I = ROWNUMBER

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

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

希望这有助于!

本文地址:IT屋 » 移动的基础上的信息在一列中的数据,然后通过数据在另一个排序