Macro to Create Tabs and Copy data

Microsoft Office excel 2007
June 23, 2010 at 08:16:25
Specs: Windows XP

I have little experience working with macros, but I would like to create a specific macro. I have a data file that is separate by rows between Accounting Numbers. Is there a way to run a macro that will separate the data by Accounting Number into different tabs? I would also like to label the tabs with the Accounting Number. Any advice would be greatly appreciated! The file looks something like this...
SSN Emp-Name Grade Plan Type
XXX-XX-XXXX John Doe 12/2 GD

SSN Emp-Name Grade Plan Type
XXX-XX-XXXX John Doe 12/2 GD

The file has thousands of rows and 15 columns. Thanks!

See More: Macro to Create Tabs and Copy data

June 23, 2010 at 08:41:59
What you are asking for can be done with a macro.

Code could be written to search for Accounting Numbers and then create tabs/copy the data for each account.

In order for us to offer specific code, we would need something more than "The file looks something like this..."

We would need some more specifics as to where the Accounting Numbers can be found (e.g. every 6th row or randomly strewn throughout the file or one row above SSN, etc.).

We would also need to know what needs to be copied and how we can identify that (e.g. one row above the Accounting Number and 2 rows below or one above and keep copying until a blank row is found, etc.)

Is it as simple as just copy everything between each blank row with the Accounting Number always being the second entry in the group?

BTW...does thousands of rows mean thousands of Accounting Numbers and therefore thousands of tabs?

Report •

June 23, 2010 at 08:59:00
Hi! Thanks for such a quick reply. Thousands of rows means thousands of employees. There is about 30 accounting numbers, however, this number could increase or decrease bi-weekly. I would need about 30 tabs. The accounting numbers are randomly strewn throughout column A. However, the data for each Accounting Number is separated by a blank row or two. The Accounting Number is in the second row (column A) of each group of employees under each Accounting Number, but the data varies in the number of employees bi-weekly. The file is essentially already separated by Accounting Number with all the employees that are paid under that number. Yes, the accounting number always appears in the row above the SSN. I would also need to include the row above the Accounting Number, which always is the word "CAN," and continue until a blank row is found. I hope this helps! If you need more information, is there a way to send you an example of the actual file? Thanks!

Report •

June 23, 2010 at 09:46:23
I've sent you an email address (via PM) to send a scaled down example of the workbook to.

Please do not share the email address with anyone.

I'll take a look at it when I get a chance, probably this evening, US-EST.

Report •

Related Solutions

June 23, 2010 at 10:17:56
I have sent an example of the file to you. I could also put the accounting number in the first column of every row if that would be easier. Thanks for your help!

Report •

Ask Question