Submit Button to transfer data to another sheet when the month is set.

[jothan4859]

Can anyone help me. I want to know how to add a code when i click submit button if the month is set to january the data will go to the January sheet or if the month is set to February the data will go to the february sheet.
Sub data_input()

ws_output = "JANUARY"

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("TIN").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("SUPPLIER").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("ADDRESS").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("AMOUNT").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("PURCHASE_MONTH").Value
Range("TIN").Value = ""
Range("SUPPLIER").Value = ""
Range("ADDRESS").Value = ""
Range("AMOUNT").Value = ""
Range("PURCHASE_DATE").Value = ""

End Sub

so far this is what i've done my mind is blowing how to make my submit button save the data in a specific sheet

 

[jothan4859]

Going and finding a script that does not work and then wanting us to modify the script so it will work with no specific details is hard to do.

As far as specific details you only said this:
In your original post:
Can anyone help me. I want to know how to add a code when i click submit button if the month is set to january the data will go to the January sheet or if the month is set to February the data will go to the february sheet.

And then posted code that does not work.

So do you think you can modify the script or do we need to start over.
If we are going to start over I need details like where will you enter certain details

and the when you click a button where do you want it to go.

Not now since I already provided code with named ranges.
So look at my code and make sure all the named ranges are correct if not modify the code or modify the named range name.
And all the named ranges should be on a sheet named "Input"
And excel looks at exact things like:
PURCHASE_MONTH

It cannot be PURCHASEMONTH

It cannot be

can we start it all over? i think i cant modify the script. sorry

i want to enter the details in blank space like C3 for TIN,C5 for SUPPLIER,C7 for address C9 for Amount and C11 for the Month

then when i click the button the data of C3,C5,C7,C9,C11 will go to another sheet where it should be like when i put January to C11 the data will go to another sheet that is named January. and when i put February to C11 the data will go to another sheet that is named February

 

[My Aswer Is This]

I'm not really excited about writing a whole new script.
So question 1
Do you know how to create a Named Range?

And did you create all the named Ranges you had in your script?
Tell me how did you create the named Range.
And were all these named Ranges on a sheet named "Input" ?

 

[My Aswer Is This]

Here I want you to create a Named Range Named "Alpha" on sheet named "Input"

And just run this script:

Sub My_Range_Check()
'Modified  2/1/2022  2:36:45 AM  EST
Sheets("Input").Range("Alpha").Value = "Yes"
End Sub

 

[jothan4859]

I'm not really excited about writing a whole new script.
So question 1
Do you know how to create a Named Range?

And did you create all the named Ranges you had in your script?
Tell me how did you create the named Range.
And were all these named Ranges on a sheet named "Input" ?

Sorry i dont really know how to create a named ranged and dont know anything bout the script commands

Yes i create all the named ranges i had in my script example for C3 i change the C3 to TIN. I just copy what i saw in youtube and change it.
yes all the named ranges is on the sheet named "Input"

 

[jothan4859]

Here I want you to create a Named Range Named "Alpha" on sheet named "Input"

And just run this script:

Sub My_Range_Check()
'Modified  2/1/2022  2:36:45 AM  EST
Sheets("Input").Range("Alpha").Value = "Yes"
End Sub

i put the macro in Test 1 and the result will go to F3 i change the F3 to Alpha

 

[dmt32]

Hi,
give following code a try & see if does what you want

Sub DataEntryToMonthDatabase()
    Dim Item        As Range, DataEntryRange As Range
    Dim wsDatabase  As Worksheet
    Dim data()      As Variant
    Dim PurchaseMonth As String
    Dim NextRow     As Long, i As Long
 
    On Error GoTo myerror
    'cells to copy from Data Entry sheet
    Set DataEntryRange = ActiveSheet.Range("C3,C5,C7,C9,C11")
 
    'purchase month (sheet name)
    PurchaseMonth = Cells(11, 3).Value
 
    'validate PurchaseMonth value is a Month Name
    If IsError(Application.Match(PurchaseMonth, Application.GetCustomListContents(4), 0)) Then
        Cells(11, 3).Select
        Err.Raise 327, , "Invalid Month Name"

    End If
 
    'size array
    ReDim data(1 To DataEntryRange.Cells.Count)
 
    'your database sheet(s)
    Set wsDatabase = ThisWorkbook.Worksheets(PurchaseMonth)
 
    For Each Item In DataEntryRange
        'build array
        i = i + 1: data(i) = Item.Value
    Next
    'output array to database range
    With wsDatabase
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(NextRow, 1).Resize(1, i).Value = data
    End With
 
    On Error Resume Next
    'clear entry
    DataEntryRange.Cells.SpecialCells(xlCellTypeConstants).ClearContents
    On Error GoTo 0
    'inform user
    MsgBox "New Record Added", 64, "New Record"
 
myerror:
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[My Aswer Is This]

I gave you a test script to just make sure your properly making named ranges.
Not sure about your answer where you said:
i put the macro in Test 1 and the result will go to F3 i change the F3 to Alpha
And I see your getting more help here so we will see if someone else can help you.
I will keep watching

 

[jothan4859]

I gave you a test script to just make sure your properly making named ranges.
Not sure about your answer where you said:
i put the macro in Test 1 and the result will go to F3 i change the F3 to Alpha
And I see your getting more help here so we will see if someone else can help you.
I will keep watching

so the script i run it on the button the TEST 1 button and the column F row 3 instead of its range as F3 i change it to Alpha as you said. as you can see on my screen shot in upper left. the cell is no longer F3 its Alpha. im not quite sure if i done it the right way