0 votes
ago in VBA by Beginner (58 points)

hi, expert i have  data in main sheet  from a2 : d   the  search  is in inputbox   based on month in col  a    then  it  should  add  a name  sheet  of month based  on what  i  write  in inputbox and  the  copy  data relevant  month   and  if  a  write  again  the  month  in inputbox then overwrite on already data is existed  and  of course  you can't add   sheet's name of  month because  is already existed    and   i would  write  more  than  month in inputbox  and  copy  data  filtered of   months  to  multiple  sheets' names  of  month ,so  if there  is  code do that  i appreciate if any body provide me




Sub SplitMonths()
    Dim rData As Range
    Dim iX As Integer
 
    With Sheets("SHEET1")
    ss = InputBox("get in month", "month")
        ''/// check if AutoFilter is on, if not swith on
        If Not .AutoFilterMode Then .Range("A1").AutoFilter
        ''/// add range to filter
        Set rData = .Cells(1).CurrentRegion
        On Error Resume Next
        For iX = 1 To 12
            rData.AutoFilter Field:=1, Operator:= _
                             xlFilterValues, Criteria2:=Array(1, iX & "/1/2020")
            '/// copy filtered data to destination sheet
            Sheets(MonthName(iX)).Cells.Clear
            .AutoFilter.Range.Copy Sheets(MonthName(iX)).Range("A1")
        Next iX
        .ShowAllData
        On Error GoTo 0
    End With
End Sub

input 

 

output  two  months  for  exeample 

 

 

 

thanks advance 

1 Answer

+1 vote
ago by Expert (801 points)
selected ago by
 
Best answer

Try this:

Sub SplitMonths()
    Dim sYear As String, sMonths() As String
    Dim msg As String, ans As String, sMths() As String
    Dim n As Integer, mth As Integer, nMths As Integer
    Dim nCol As Long, oWS As Worksheet
    
    Const myName As String = "SplitMonths"
    Const dataSheet As String = "SHEET1"
    Const firstCol As Long = 1      ' col A
    Const lastCol As Long = 4       ' col D
    
    sMonths = Split("xxx,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC", ",")
    sYear = " " & Year(Date)        ' split data for current year only
    Worksheets(dataSheet).Activate  ' assume data starts with header in row 1
    msg = "To split " & dataSheet & " data for" & sYear & " by month, enter " _
        & "a month or several months separated by commas." & vbNewLine _
        & "Examples: March or Mar,Apr or 3,4,MAY,june"
    ans = InputBox(msg, myName)
    If ans = vbNullString Then Exit Sub
    sMths = Split(ans, ",")         ' returns a zero-based array
    nMths = UBound(sMths)
    On Error Resume Next
        For n = 0 To nMths
            mth = Month(sMths(n) & sYear)
            If Err <> 0 Then
                msg = "You entered: '" & ans & "'" & vbNewLine _
                    & "Error: '" & sMths(n) & "' is not a valid month"
                MsgBox msg, vbCritical, myName
                Exit Sub
            End If
            sMths(n) = sMonths(mth)
        Next n
        For n = 0 To nMths
            Set oWS = Worksheets(sMths(n))
            If Err <> 0 Then
                Err.Clear
                Set oWS = Worksheets.Add(, Worksheets(Worksheets.Count))
                oWS.Name = sMths(n)
            End If
            Worksheets(dataSheet).Activate
            oWS.Cells.ClearContents
            For nCol = firstCol To lastCol
                oWS.Columns(nCol).ColumnWidth = Columns(nCol).ColumnWidth
            Next nCol
        Next n
    On Error GoTo 0
    With Worksheets(dataSheet)
        For n = 0 To nMths
            Range(.Columns(firstCol), .Columns(lastCol)).AutoFilter Field:=1, _
                Operator:=xlFilterValues, Criteria2:=Array(1, sMths(n) & sYear)
            .AutoFilter.Range.Copy Worksheets(sMths(n)).Cells(1, firstCol)
            .ShowAllData
        Next n
    End With
    msg = dataSheet & " data for" & sYear & " has been split to"
    For n = 0 To nMths
        msg = msg & " " & sMths(n)
    Next n
    msg = msg & ". You can rearrange the sheets if necessary."
    MsgBox msg, vbInformation, myName
End Sub

Does this answer your question?

ago by Beginner (58 points)
thanks  JWolley  actually  i tested  your  code   it  just  add  sheet's name  of  month without   copy  data  of  month  no  data   in sheet's month i  hope  fix it  

thanks advance
ago by Expert (801 points)

Is your data in 'SHEET1'!$A:$D starting in row 1? Does it look like the picture in your original question? Are you trying to split months that don't exist in the data? Is your data for year 2020? Does your date format match your Settings > Time & Language > Region? 

I tested using the data illustrated below (with USA Region date format):

ago by Beginner (58 points)
yes,  you're right about   empty data  in month   your  code  works   like   charm    thanks   for   the  awesome  code
ago by Expert (801 points)

You might also be interested in My Excel Toolbox.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...