SOLVED

# Get data from input form, send it to a worksheet and row based on form values

I have a workbook with a data input form (for entering the sales numbers for today) which has a cell with

``=TEXT(TODAY(),"mmm")``

to display the (short/3-character) current month and a similar one to display the day of the month. In the same workbook, I have 12 sheets, 1 for each month - JAN, FEB, MAR, etc.

I want to put a macro button on the sheet that will

1. Print the form
2. Transfer the values from various form fields to the appropriate columns of the corresponding row of the corresponding worksheet.
3. Clear the form and save the workbook

I'm pretty sure I can figure out how to select the data and write it to the proper column, but I have no idea how to create the variable(s) for referencing the row and worksheet.

2 Replies
best response confirmed by DonnyBahama (Copper Contributor)
Solution

# Re: Get data from input form, send it to a worksheet and row based on form values

Let's say that the cell with the month formula is A2 and the cell with the day formula is A3

And let's say that the month sheets have a header row, so day 1 is in row 2 etc.

``````    Dim w As Worksheet
Dim r As Long
Set w = Worksheets(Range("A2").Value)
r = Range("A3").Value + 1
' Populate a cell in column D of the r-th row of worksheet w
w.Range("D" & r).Value = ...``````

# Re: Get data from input form, send it to a worksheet and row based on form values

@Hans VogelaarThank you so much! That was very helpful! That code wrote the specified cell value to row 10, column D of the AUG sheet. (Today being the 10th of August.) You were right - the AUG sheet does have a header row. It took some trial and error (I'm very much a VBA novice) to figure out how to increment today's day number but I figured it out. This code worked perfectly:

``````    Dim ws As Worksheet
Dim rn As Byte
' B3 is the cell containing the month value. It gives us the destination WORKSHEET
Set ws = Worksheets(Range("B3").Value)
' A3 is the cell with the day of the month. It gives us the destination ROW NUMBER
' which we need to increment by 1 to allow for the header row
rn = Range("A3").Value + 1
' Populate a cell in column D of the rn-th row of worksheet ws with the data from
' row 5, column 4
ws.Range("D" & rn).Value = Range("B6")``````