VBA Auto Fill a Column with name based on cell value

[lounatix]

Firstly, i apologize if the title doesn't quite match my request i was unsure of how to word it or if in the wrong place or anything (1st post)

I use an excel sheet to assign work to 5 individuals and on certain days this involves me pasting someones name 100 times and so forth.

I was wondering if there is a VBA code which would paste an individuals name, Say "Mark" in Column G based on a number value in say cell A2.
So if i input "89" in cell A2 it would paste "Mark" in column "H" 89 times? I also need this code to work for 4 more names so say if i input "45" in A3 then it would paste "Joe" and so forth.

Thank You!

 

[portews]

Here you go, cheap and dirty. Put the code in the ThisWorkbook object or a Module if you want one:

Sub CopyName()
Dim myNum As Integer
'click in the cell you want to copy and run this code

'will open an inputbox to allow input
myNum = Application.InputBox(prompt:="Enter Total Number of Copies", Title:="Copy Current Cell Contents Down", Type:=1)

'if the cancel is pressed
If myNum = False Then Exit Sub

'copies the active cell
ActiveCell.Copy

'pastes the active cell from the active cell down to the Active cell plus the number of copies you wanted
ActiveSheet.Paste Destination:=ActiveSheet.Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row - 1 + myNum, ActiveCell.Column))
End Sub

Go to the Macros listing in Excel and select the CopyName from the list. Click options and give it a shortcut key like Ctrl+Shift+C.

Put in the name you want in a cell where you want to start a column and leave it selected. When you hit Ctrl+Shift+C a box will pop up and ask you how many total copies you want. When you click OK, it will copy them down the column that many rows.

You can get a lot fancier, of course. For example you can make a floating userform with a dropdown for the names and a textbox for the number, but this will be the easiest way.