How to automatically add repeating data to the next row in same column without retyping

[vj49]

I need help writing VBA code for data input userform.

I have a worksheet with 8 columns. The first 4 columns (A:D) are used to input business names and healthplan data:
BusinessName, HealthPlan, GroupNumber, ZipCode

The next 4 columns (E:H) are used for individual employee data:
EmpName, EmpIID, Relationship, Age.

For each business name there may be one or many different employees. Whenever there is a new employee in the same business, I want the data in the first 4 columns to repeat into each new row without retyping the same data, and to continue to repeat until another new business name is entered.

I've created a userform that works great to add the data but I can't figure out how to write the code to let me add duplicate information without retyping. Any suggestions will be greatly appreciated. Thanks!

[PeteABC123]

Try this:
Re: Automatically add repeating data

Select your range, and format as General
Highlight all the cells that need attention. lets say, A2:D1000. This includes all existing cells containing values.
Now, press F5.
Select Special
Select Blanks
Press OK
Press the press the = key
press and hold down the CTRL key
press the arrow up key (still holding down the CTRL key. . .don't let go. . .)
press enter.
copy and paste the entire range as values.
You can record this method into VBA and use call to invoke it during the procedure.


Pete

[vj49]

Thank you. That works if I go back and manually manipulate the worksheet. What I'm hoping for is code to have the repeating cells automatically be copied and pasted into the next empty row when triggered by a command button on the User form. I don't want to be entering blank spaces in any of the rows of new data.