Sheet sorting/naming problem

**gwithey** · 01-19-2009, 06:05 AM

I have some code that sorts rows of data into a varied number new excel sheets. (named with month names ie jan_06).

This works by looking at the data source, creating sheets if none exsist and adding the data row to it.

Else creating a new Sheet to acomodate that month, until sheet 3 is sorted.

The problem being that the naming of sheet tabs are not quite working.

Data gets sorted into jan 05 for example weither the data is jan 05 or 06.

The problem may lie in This line, i am not sure. Can anyone see the problem.

Please Login or Register  to view this content.

Whole macro:

Please Login or Register  to view this content.

Thanx George

**royUK** · 01-19-2009, 06:31 AM

Why not use a PivotTable instead?

**gwithey** · 01-19-2009, 06:50 AM

I tried that before this code is great it just doesnt name the sheets properly when it creates them.

Just jan_05, Jan_06 and Jan_07 somehow end up in the same sheet named Jan_05 not seperate ones. Will create 12 sheets for each month of the year but not do the cycle again correctly when more than 12 months.

George

**royUK** · 01-19-2009, 07:03 AM

Are you using the UDF - wsExists?

Your error handler seems to be deleting sheets

**gwithey** · 01-19-2009, 07:12 AM

Yes i am using ws Exists.

The error handler deletes sheets as on error a blank sheet4 is created.
I used delete sheet to save the user having to do this

**royUK** · 01-19-2009, 07:23 AM

I can't see why it should adds sheet4.

Can you attach an example workbook?

**gwithey** · 01-19-2009, 07:29 AM

Example.

Is just the month names that need to be correct when macro executed

**gwithey** · 01-19-2009, 07:34 AM

For example if you put a date 01/01/09 in E2 and 01/01/2010 in E3

Enter something in A2 (anything)

Then execute you will see the problem

George

**royUK** · 01-19-2009, 09:04 AM

A2 of which sheets?

**gwithey** · 01-19-2009, 09:12 AM

needs to be in Sheet3

**royUK** · 01-19-2009, 09:50 AM

The problem I'm getting is it doesn't seem to extract the year from the date

**gwithey** · 01-19-2009, 11:15 AM

I have found the solution

I updated the line to this:

Please Login or Register  to view this content.

The format was throwing it out as was the date which returned current date not date of the cell.

The above line names the sheets correctly.

Thanx for your time

George

Sheet sorting/naming problem

LinkBack

Thread Tools

Rate This Thread

Display

Sheet sorting/naming problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions