Excel VBA Course
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

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
excel
MS Office Version: 2016
posted by: Chip_Excel on Wed Jan 5, 2022 at 4:53 pm
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
posted by: John_Ru (rep: 6142) on Thu Jan 6, 2022 at 2:13 pm
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: 6142) Jan 6, '22 at 4:09 pm
Did that work?
John_Ru (rep: 6142) 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