copy data by inputbox based on month

[abdelfattah]

hi,
i hope some body help to get code transfer data from sheet1 to sheet's month by inputbox
input

output

 

[Mentor82]

Hi,
I'm afraid I do not get what you need. In the input box you type in May and how did you come up with row 5 as a result? Can you explain the logic for that?

 

[abdelfattah]

hi, mentor

i have many dates about may,june ,july in sheet1 when i write may into inputbox ,then copy data only relevant month may to sheet called may and so on the rest of months

 

[Fluff]

@abdelfattah
How many times do you need to be told about cross-posting without supplying links?
Please supply the links.

 

[Mentor82]

Hi,

Code of button on form to copy data based on typed in month in text box.
I named by copy button on the form cb_Copy whereas the textbox where the month is typed in I named tb_month. Type this procedure under your copy button and rename the either button or name of the procedure accordingly to your button name "buttonName_Click()"

Private Sub cb_Copy_Click()
    CopyMonthsData strMonth:=Me.tb_month.Text
End Sub

Here's the procedure that copies the data according to your needs, I guess, based on the provided month in the text box on the form. Copy and past this procedure to your module.

Sub CopyMonthsData(strMonth$)
    Dim i&
    Dim lRow&
    Dim ws As Worksheet
    Dim ws2CopyTo As Worksheet
    Dim boolWsExists As Boolean
    Dim monthNo&
    Dim newRow&
    Const mainWsName$ = "SHEET1"
    
    boolWsExists = False
    
    If strMonth = vbNullString Then
        MsgBox "Month was not provided", vbCritical, "InfoLog"
        Exit Sub
    End If
    
    Select Case LCase(strMonth)
        Case Is = "january"
            monthNo = 1
        Case Is = "february"
            monthNo = 2
        Case Is = "march"
            monthNo = 3
        Case Is = "april"
            monthNo = 4
        Case Is = "may"
            monthNo = 5
        Case Is = "june"
            monthNo = 6
        Case Is = "july"
            monthNo = 7
        Case Is = "august"
            monthNo = 8
        Case Is = "september"
            monthNo = 9
        Case Is = "october"
            monthNo = 10
        Case Is = "november"
            monthNo = 11
        Case Is = "december"
            monthNo = 12
        Case Else:
            monthNo = 0
    End Select
        
    If monthNo = 0 Then
        MsgBox "The privided text is not a month: " & strMonth, vbCritical, "InfoLog"
        Exit Sub
    End If
    
    With Worksheets(mainWsName)
        lRow = .Range("A1").CurrentRegion.Rows.Count
        
        If lRow = 1 Then
            MsgBox "No data found!", vbCritical, "InfoLog"
            Exit Sub
        End If
        
        'find if typed in worksheet exists
        
        For Each ws In Worksheets
            If LCase(ws.Name) = LCase(strMonth) Then
                Set ws2CopyTo = ws
                boolWsExists = True
                Exit For
            End If
        Next ws
        
        If boolWsExists = False Then
            MsgBox "Typed in worksheet does not exist: " & strMonth, vbCritical, "InfoLog"
            Exit Sub
        End If
        
        
        For i = 2 To lRow
            If Month(.Cells(i, "A")) = monthNo Then
                newRow = ws2CopyTo.Range("A1").CurrentRegion.Rows.Count + 1
                .Cells(i, "A").EntireRow.Copy ws2CopyTo.Cells(newRow, "A")
            End If
        Next i
    End With
    
    MsgBox "Done", vbInformation, "InfoLog"
        
End Sub

Check this out and I'd appreciate if you'd let me know whether that's what you're looking for

 

[Mentor82]

Hi buddy,
I'm sorry but somhow I did not noticed that you're using input box for typing the month and prepared for you a code that proceeds the month text from form not from input box.
Here I've corected the code for you so it uses input box like in the example you made. Within the code I assign to the inputbox dynamically name of the current month.
Copy and paste to your module, assign it to your button and let me know wheter it's fine for you .

Sub CopyMonthsData()
    Dim i&
    Dim lRow&
    Dim ws As Worksheet
    Dim ws2CopyTo As Worksheet
    Dim boolWsExists As Boolean
    Dim monthNo&
    Dim newRow&
    Dim strMonth$
    Const mainWsName$ = "SHEET1"
    
    boolWsExists = False
    
    strMonth = Application.InputBox("Type in a month", "Data Entry", Default:=Format(Month(Date), "mmmm"))
    
    If strMonth = vbNullString Then
        MsgBox "Month was not provided", vbCritical, "InfoLog"
        Exit Sub
    End If
    
    Select Case LCase(strMonth)
        Case Is = "january"
            monthNo = 1
        Case Is = "february"
            monthNo = 2
        Case Is = "march"
            monthNo = 3
        Case Is = "april"
            monthNo = 4
        Case Is = "may"
            monthNo = 5
        Case Is = "june"
            monthNo = 6
        Case Is = "july"
            monthNo = 7
        Case Is = "august"
            monthNo = 8
        Case Is = "september"
            monthNo = 9
        Case Is = "october"
            monthNo = 10
        Case Is = "november"
            monthNo = 11
        Case Is = "december"
            monthNo = 12
        Case Else:
            monthNo = 0
    End Select
        
    If monthNo = 0 Then
        MsgBox "The privided text is not a month: " & strMonth, vbCritical, "InfoLog"
        Exit Sub
    End If
    
    With Worksheets(mainWsName)
        lRow = .Range("A1").CurrentRegion.Rows.Count
        
        If lRow = 1 Then
            MsgBox "No data found!", vbCritical, "InfoLog"
            Exit Sub
        End If
        
        'find if typed in worksheet exists
        
        For Each ws In Worksheets
            If LCase(ws.Name) = LCase(strMonth) Then
                Set ws2CopyTo = ws
                boolWsExists = True
                Exit For
            End If
        Next ws
        
        If boolWsExists = False Then
            MsgBox "Typed in worksheet does not exist: " & strMonth, vbCritical, "InfoLog"
            Exit Sub
        End If
        
        
        For i = 2 To lRow
            If Month(.Cells(i, "A")) = monthNo Then
                newRow = ws2CopyTo.Range("A1").CurrentRegion.Rows.Count + 1
                .Cells(i, "A").EntireRow.Copy ws2CopyTo.Cells(newRow, "A")
            End If
        Next i
    End With
    
    MsgBox "Done", vbInformation, "InfoLog"
        
End Sub

 

[abdelfattah]

hi, fluff i know this condition in this forum , i would recommend my apology about this forgive me i was hurry to solve my problem , i was ready issue my problem in many forums to solve my post i would say this doesn't repeat again
this is my post in this forum
copy data from sheet to another based on month using inputbox with arrays - OzGrid Free Excel/VBA Help Forum

 

[Fluff]

Thanks for the link.

i would say this doesn't repeat again

I hope so.

 

[abdelfattah]

i'm sorry about delaying answer thanks Mentor great code

 

[Mentor82]

i'm sorry about delaying answer thanks Mentor great code

My pleasure ?. Thanks for your feedback.