Splitting data into multiple tabs

**jadams0550** · 12-16-2011, 08:13 AM

Hi

have a large sheet of data (500k rows) with week ending dates in column A. I need a macro that will split that data into a tab for each week ending date and rename the sheet with the relevant week ending date.

Have seen a few macros out there but can't seem to get them to work.

Can anyone help me?

small sample attached now

Edit: It was brought to my attention that this attachment may have contained sensitive information. It has been removed. Please make sure to scrub what you attach clean of confidential info and re-post it.

**arlu1201** · 12-16-2011, 08:18 AM

It will be good if you attach a sample workbook and we can assist you easily.

**Kyle123** · 12-16-2011, 10:28 AM

Give this a whirl

Please Login or Register  to view this content.

**jadams0550** · 12-21-2011, 09:36 AM

Thanks for this, but when I run it nothing happens, do i need to name the worksheet a specific name? Have attached some sample data in the format of the report. Note that the data is made up and not sensitive so please dont report or remove..thanks!

John

**Alf** · 12-21-2011, 11:39 AM

Perhaps this works for you?

To test it run the macro "Split_Date".

Alf

**jadams0550** · 01-05-2012, 11:10 AM

Thanks very much, initally looks promising however when I run it on my larger sheet it loops endlessly creating several hundred tabs. it creates the initial tabs for my weekending dates really easily but then just keeps running.

Any ideas how to make it stop doing this?

**Alf** · 01-06-2012, 06:47 AM

when I run it on my larger sheet it loops endlessly creating several hundred tab

Sorry, my bad. Rereading your request I see that you wanted a tab for each week and not one for every day.

Try this file and see if it works better for you.

Not to happy with the excel function “WEEKNUM” as it does not give the same result as my Swedish calendar.

WEEKNUM comes in 2 flavors

formula =WEEKNUM(Serial_num, 2) Weeks begin on Monday
formula = WEEKNUM(Serial_num, 1) Weeks begin on Sunday

I do assume that you want the weeks beginning on Monday, if not so change appropriate line in macro.

If your data spans over several years you should perhaps consider using the "MONTH" function instead of "WEEKNUM" this would reduce the number of tabs with a factor of four.

Alf