Seperating Months by Sheets

[boxboy30]

I have one spreadsheet that has 12months of information. How do I write a macro that will divide the months up and seperate them by sheets into the same workbook? Is there a way to automatically do it without copying and pasting?

 

[sheetspread]

Yes, a VBA loop of the advanced filter or page splitter in a pivot table. Stand by for the code.........

 

[sheetspread]

Sub PagesByDescription()

Dim rRange As Range, rCell As Range

Dim wSheet As Worksheet

Dim wSheetStart As Worksheet

Dim strText As String



    Set wSheetStart = ActiveSheet

    wSheetStart.AutoFilterMode = False

    'Set a range variable to the correct item column

    Set rRange = Range("A1", Range("A65536").End(xlUp))

    

        'Delete any sheet called "UniqueList"

        'Turn off run time errors & delete alert

        On Error Resume Next

        Application.DisplayAlerts = False

        Worksheets("UniqueList").Delete

        

        'Add a sheet called "UniqueList"

        Worksheets.Add().Name = "UniqueList"

        

           'Filter the Set range so only a unique list is created

            With Worksheets("UniqueList")

                rRange.AdvancedFilter xlFilterCopy, , _

                 Worksheets("UniqueList").Range("A1"), True

                 

                 'Set a range variable to the unique list, less the heading.

                 Set rRange = .Range("A2", .Range("A65536").End(xlUp))

            End With

            

            On Error Resume Next

            With wSheetStart 

            	For Each rCell In rRange

                  strText = rCell

                 .Range("A1").AutoFilter 1, strText

                    Worksheets(strText).Delete

                    'Add a sheet named as content of rCell

                    Worksheets.Add().Name = strText

                    'Copy the visible filtered range _

                    (default of Copy Method) and leave hidden rows

                    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")

                    ActiveSheet.Cells.Columns.AutoFit

                Next rCell

            End With

            

        With wSheetStart 

        	.AutoFilterMode = False

            .Activate

        End With

        

        On Error GoTo 0

        Application.DisplayAlerts = True

End Sub

 

[boxboy30]

and what EXACTLY does this do? Can I add it to my existing code?

 

[sheetspread]

I'm using excel 2003 and that above macro isn't working so let me post one that does:

Option Explicit
Sub Splitdatatosheets()
'
' Splitdatatosheets Macro
'
'
Dim rng As Range
Dim rng1 As Range
Dim vrb As Boolean
Dim sht As Worksheet
Set rng = Sheets("Sheet1").Range("A1")
Set rng1 = Sheets("Sheet1").Range("A1:D1")
vrb = False
Do While rng <> ""
For Each sht In Worksheets
If sht.Name = rng.Value Then
sht.Select
Range("A2").Select
Do While Selection <> ""
ActiveCell.Offset(1, 0).Activate
Loop
rng1.Copy ActiveCell
ActiveCell.Offset(1, 0).Activate
Set rng1 = rng1.Offset(1, 0)
Set rng = rng.Offset(1, 0)
vrb = True
End If
Next sht
If vrb = False Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = rng.Value
Sheets("Sheet1").Range("A1:c1").Copy ActiveSheet.Range("A1")
Range("A1").Select
Do While Selection <> ""
ActiveCell.Offset(1, 0).Activate
Loop
rng1.Copy ActiveCell
Set rng1 = rng1.Offset(1, 0)
Set rng = rng.Offset(1, 0)
End If
vrb = False
Loop
End Sub

Now put this in sheet 1 and run the macro:

Name Number Color
January 34554 Red
June 7856 Orange
July 56876 Yellow
August 445 Green
September 76856 Blue
October 876978 Indigo
November 7656 Violet
May 56565 Red
November 84847.5 Orange
December 127271.25 Yellow
January 190906.875 Green
March 286360.3125 Blue
April 429540.4688 Indigo
April 644310.7031 Blue
March 966466.0547 Red
April 1449699.082 Orange
May 2174548.623 Yellow
June 3261822.935 Green
July 4892734.402 Blue
August 7339101.603 Blue
September 11008652.4 Blue
October 16512978.61 Blue
November 24769467.91 Green
December 37154201.86 Blue
January 55731302.8 Indigo
February 83596954.19 Violet
March 125395431.3 Blue
April 188093146.9 Blue
May 282139720.4 Blue


It will filter each month into a new worksheet, and retain the master. Limits: Tab sorting won't be by month, it assumes the dates are by text month (e.g. 3/16/2011 or 5/13/2011 won't be read as March or May).

You can call this macro from another one or modify the code so it doesn't end before moving to another step.