Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

VBA Input Form Data to Specified Worksheet

0

Hi Everyone,

First post here, and happy new year to everyone!

I am very much a novice at VBA and am trying to create a small macro.

I have a file with multiple worksheets for different users. All the worksheets are identical, but each user is putting different data into the table

I want to create a Worksheet with a form and macro button that will paste the form data into the next empty table row on a worksheet that the user selects in cell B2 via a dropdown data validation list.

So, "User 1" will select "User 1" in the B2 drop-down, push the button and the data will go into the "User 1" worksheet's next empty row. "User 2" would select "User 2" in the B2 drop-down and the data would go to "User 2" worksheet's next empty row. THis would be across approx eight different users.

I found a VBA code that does exactly what I want, but only to a specified worksheet. I am wondering if there is a way to change the formula so that the the worksheet name changes to whatever is in cell B2. 

Here is what I have:

Sub price_input()
ws_output = "B2"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("salesman").Value
 Sheets(ws_output).Cells(next_row, 2).Value = Range("EU").Value
 Sheets(ws_output).Cells(next_row, 3).Value = Range("platform").Value
 Sheets(ws_output).Cells(next_row, 4).Value = Range("style").Value
 Sheets(ws_output).Cells(next_row, 5).Value = Range("trial").Value
 Sheets(ws_output).Cells(next_row, 6).Value = Range("custcode").Value
 Sheets(ws_output).Cells(next_row, 7).Value = Range("salesman").Value
 Sheets(ws_output).Cells(next_row, 8).Value = Range("cw").Value
 Sheets(ws_output).Cells(next_row, 9).Value = Range("min").Value
 Sheets(ws_output).Cells(next_row, 10).Value = Range("year").Value
 Sheets(ws_output).Cells(next_row, 11).Value = Range("quar").Value
 Sheets(ws_output).Cells(next_row, 12).Value = Range("price").Value
 Sheets(ws_output).Cells(next_row, 13).Value = Range("confirmdate").Value
Answer
Discuss

Answers

0

Hi Chip and welcome to the Forum

Provided you have a valid sheet name in cell B2 of Sheet1 (and the named ranges you refer to), the modifed code below will work. Note that it uses a With/ End With statement to save retyping ws_output 13 times (so a lead dot/period replaces the With argument (ws_output). Key changes in bold:

Sub price_input()

Dim ws_output As Worksheet

Sheets(Sheet1).Activate ' so Range values refer to Sheet1

Set ws_output = Sheets(Sheet1.Range("B2").Value)

next_row = ws_output.Range("A" & Rows.Count).End(xlUp).Row + 1
With ws_output
    .Cells(next_row, 1).Value = Range("salesman").Value
    .Cells(next_row, 2).Value = Range("EU").Value
    .Cells(next_row, 3).Value = Range("platform").Value
    .Cells(next_row, 4).Value = Range("style").Value
    .Cells(next_row, 5).Value = Range("trial").Value
    .Cells(next_row, 6).Value = Range("custcode").Value
    .Cells(next_row, 7).Value = Range("salesman").Value
    .Cells(next_row, 8).Value = Range("cw").Value
    .Cells(next_row, 9).Value = Range("min").Value
    .Cells(next_row, 10).Value = Range("year").Value
    .Cells(next_row, 11).Value = Range("quar").Value
    .Cells(next_row, 12).Value = Range("price").Value
    .Cells(next_row, 13).Value = Range("confirmdate").Value
End With

End Sub
The typing can be reduced further if the With statement uses the cell in column A then the indented lines use Offsets to refer to other columns in the same row. Changes in bold again:
Sub price_input()

Dim ws_output As Worksheet
Sheets(Sheet1).Activate
Set ws_output = Sheets(Range("B2").Value)

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

With ws_output.Cells(next_row, 1)
    .Value = Range("salesman").Value
    .Offset(0, 1).Value = Range("EU").Value
    .Offset(0, 2).Value = Range("platform").Value
    .Offset(0, 3).Value = Range("style").Value
    .Offset(0, 4).Value = Range("trial").Value
    .Offset(0, 5).Value = Range("custcode").Value
    .Offset(0, 6).Value = Range("salesman").Value
    .Offset(0, 7).Value = Range("cw").Value
    .Offset(0, 8).Value = Range("min").Value
    .Offset(0, 9).Value = Range("year").Value
    .Offset(0, 10).Value = Range("quar").Value
    .Offset(0, 11).Value = Range("price").Value
    .Offset(0, 12).Value = Range("confirmdate").Value
End With

End Sub
Hope this helps
Discuss

Discussion

Hi John, 
Thank you for the answer. 
Where it says "Sheets(Sheet1).Activate", do I need to change anything to the sheet where the macro is running?

I am running this macro of a sheet named "Input Sheet"

"B2" is a drop down list of all worksheet names in the file. 

Chip
Chip_Excel Jan 6, '22 at 3:25 pm
Chip. Change it to
Sheets("Input Sheet").Activate 
. The dropdown in cell B2 is a good idea. 
John_Ru (rep: 2867) Jan 6, '22 at 4:09 pm
Did that work?
John_Ru (rep: 2867) Jan 10, '22 at 1:39 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login