Sheet sorting/naming problem

[gwithey]

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.

If IsDate(cl.Value) Then Sht = MonthName(Month(cl.Value)) & "_" & Format(Year(Date), "yy")

Whole macro:

    Dim Sht    As String
    
    On Error GoTo ErrorHandlingSortDataToMonths 
    
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False     
            .Calculation = xlCalculationManual
  
            Set shtData = Sheet3  
            
                With shtData
                    Set rng = .Range(.Cells(2, 5), .Cells(.Rows.Count, 5).End(xlUp))  
                   For Each cl In rng
                        If IsDate(cl.Value) Then Sht = MonthName(Month(cl.Value)) & "_" & Format(Year(Date), "yy")  
                        If Not wsExists(Sht) Then                                                      
                            Worksheets.Add After:=Worksheets(Worksheets.Count)
                            ActiveSheet.Name = Sht
                            'add header row                        
                            .Cells(1, 1).EntireRow.Copy Sheets(Sht).Cells(1, 1) 'Then add the row of data
                        End If
                        If Not IsEmpty(cl) Then cl.EntireRow.Copy Sheets(Sht).Cells(Rows.Count, 1).End(xlUp).offSet(1, 0)
                    Next cl                                              
                    .Select 
                End With
            
            Worksheets(1).Select

                .DisplayAlerts = True
                .ScreenUpdating = True              
                .Calculation = xlCalculationAutomatic
        End With

                
ErrorHandlingSortDataToMonths:
    
Dim ErrorSht    As Worksheet
Dim Msg As String
    
    Select Case Err
        Case 1004: 'Data may not have been imported to data sheet
            Msg = "An Error Has Occured" & vbNewLine
            Msg = Msg & "Check that there is data in sheet 3 (Data) and retry"    
            MsgBox Msg
            
            For Each ErrorSht In ThisWorkbook.Worksheets
                If ErrorSht.Index > 3 Then ErrorSht.Delete          
            Next ErrorSht
            
            Worksheets(1).Select
        
        Exit Sub
    Case Else
End Select

Call AddFormulas
        
        
End Sub

Thanx George

[royUK]

Why not use a PivotTable instead?

[gwithey]

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]

Are you using the UDF - wsExists?

Your error handler seems to be deleting sheets

[gwithey]

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]

I can't see why it should adds sheet4.

Can you attach an example workbook?

[gwithey]

Example.

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

[gwithey]

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]

A2 of which sheets?

[gwithey]

needs to be in Sheet3

[royUK]

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

[gwithey]

I have found the solution

I updated the line to this:

If IsDate(cl.Value) Then Sht = MonthName(Month(cl.Value)) & "_" & (Year(cl.Value))

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