copy data by inputbox based on month

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,494
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hi,
i hope some body help to get code transfer data from sheet1 to sheet's month by inputbox
input
1.JPG



output
2.JPG
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
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
 
Upvote 0
@abdelfattah
How many times do you need to be told about cross-posting without supplying links?
Please supply the links.
 
Upvote 0
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()"
VBA Code:
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.
Code:
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 :)
 
Upvote 0
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 :).

VBA Code:
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
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top