Trying to create a new form using Vba and Macros

[Ayush225]

While I'm new to this but watched and tried a lot for this project.

I have a requirement to create a form that looks like this, well I created the dropdown and the button but to have the data in a way it should be is something I'm unable to do it. Basically, one will select the company from the dropdown and then enter the data for each question and quarter. Then once everything is done hit the submit button to grab that data and reset it as this will be used for multiple companies. 

Once this is done the output should look like this

[#2]

@Ayush225 

You've had 75 views (and counting) without any replies. That kind of disparity usually means that what you're asking is unclear. That's definitely the case here. For starters, the data you say you want to collect isn't provided for in the supposed "output" and some things are in that "output" that aren't accounted for in the input. In other words, there are a lot of major gaps that make the whole situation hard to follow, and therefore hard to respond to.

You would be more likely to get some help if you could describe more fully the full process, not in Excel terms (or with rough and inaccurate mockups that are incomplete).

• Describe in plain English vocabulary such things as: who is to be entering the raw data; what that raw data consists of, where it comes from, how frequently it comes, etc.
• Then what the final result is to be (i.e., how is the data to be used and for what purpose; not how it is to be stored or manipulated while waiting to serve the ultimate purpose).
• And anything else that would be what people typically call "the big picture." 

[#3]

@Ayush225 

Beyond what mathetes wrote, I have some questions/comments:

• Clarity of term: Is the intended data related to services (e.g., construction, cleaning) or is it related to units (groups of people who perform services)? I ask because the terms "Unit Service 1", "Unit Service 2", etc., look like services, but the column heading in your second screenshot is "Units" (not "Services").
• Meaning of "form": Are you intending to create a UserForm? A good introduction to the topic is here.  A sample of what such a UserForm might look like is here (within the red rectangle), shown in design mode:

• Process design: Is the number of "Unit Services" (or maybe "Service Units") always three per company (as it might be if the data is obtained during an audit/review of the company? Or does the number vary by company? If the number varies by company, is the number stored in (or accessible from) the workbook, before the user begins the data entry? The answers would affect how you would design the user input process.
• Process design: Are the three data values ("questions") shown--Number of Sales, Number of People, Number of Devices--the only values to be captured at this time? Or are those just three examples of a larger set? Do the "questions" vary by company?
• Data design: Are you sure you want the "Company Size" data to be stored with every question+value? That size information will not vary with each captured data value; it should only be dependent on the Quarter (as a company may grow or shrink over time).
• I'm unsure why you included an "Excel for web" tag and a "Macros and VBA" tag. VBA cannot be used in Excel for the web. (I don't know whether Office Script is usable in Excel for the web.)