Using Macro to separate a spreadsheet and create tabs for each month

**ajang** · 10-26-2012, 01:09 AM

Hi,

Can some please help, separating and creating tabs within a single spreadsheet based on month?

I actually download a full file which has months from Jan to June or sometimes Jan. to Dec. I have to separate and create different tabs for each month. I copy paste them manually (file attached). Is there a way to do it using Macro?

Thanxxx a lot in advance,
Ajang

**Armando Montes** · 10-26-2012, 01:21 AM

One way here:
http://www.contextures.com/AdvFilterRepFiltered.zip

**Trebor76** · 10-26-2012, 01:42 AM

Hi ajang,

Try this:

Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    Dim rngCell As Range
    Dim wstSourceTab As Worksheet
    Dim lngPasteRow As Long
    Dim strTest As String
    
    Set wstSourceTab = Sheets("download-14") 'tab containing raw data. Change to suit.
    
    Application.ScreenUpdating = False
    
    For Each rngCell In wstSourceTab.Range("J2:J" & wstSourceTab.Range("J" & Rows.Count).End(xlUp).Row)
        'Create any tabs that don't already exist
        On Error Resume Next
            strTest = Sheets(CStr(Format(rngCell, "mmmm"))).Range("A1")
            If Err.Number = 9 Then 'An error number of 9 indicates that no such tab exists
                Worksheets.Add after:=Worksheets(Worksheets.Count)
                Worksheets(Worksheets.Count).Name = Format(rngCell, "mmmm")
                'Put headers in
                Sheets(CStr(Format(rngCell, "mmmm"))).Range("A1:O1").Value = wstSourceTab.Range("A1:O1").Value
            End If
        On Error GoTo 0
        'Copy the data from the 'wstSourceTab' tab to the relevant month tab
        lngPasteRow = Sheets(CStr(Format(rngCell, "mmmm"))).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Sheets(CStr(Format(rngCell, "mmmm"))).Range("A" & lngPasteRow & ":O" & lngPasteRow).Value = wstSourceTab.Range("A" & rngCell.Row & ":O" & rngCell.Row).Value
    Next rngCell
    
    Application.ScreenUpdating = True
    
    MsgBox "The data has now been copied from the """ & wstSourceTab.Name & """ tab across each applicable tab.", vbInformation, "Data Distribution Editor"
    
    Set wstSourceTab = Nothing
    
End Sub

Regards,

Robert