Forum Discussion
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
- Print the form
- Transfer the values from various form fields to the appropriate columns of the corresponding row of the corresponding worksheet.
- 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.
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 = ...Does that help you on your way?
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 = ...Does that help you on your way?
HansVogelaarThank 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")
