Excel VBA copy data to a worksheet in list form

[NewbieExcel123]

Hi all,

Newbie here, I'm trying to use VBA to copy a set of data to a long list, not sure how I would do this.

I'm wanting to copy the data as below into another worksheet where 20 or so people have their names in columns with a column for a date to the left of each reference.

I would like excel to copy the date (which will always be the current date) before each case reference into the column to the left of the person and then add in the 5 reference numbers, but depending on the name selected from the User drop down list the data would need to be pasted into the corresponding column for that person.

Choose User	Joe Bloggs
	Enter references:
	123
	456
	789
	987
	665
Date:	31/10/2024
	Submit

This is the other worksheet where the VBA needs to look up the person and past the references into their column and pop the date to the left of each reference.

	John		Peter		Sally		Pete
Date		Date		Date		Date		Date

So far I've got this:

Private Sub Commandbutton1_Click()
Application.ScreenUpdating = False


Dim xSheet As Worksheet
Set xSheet = ActiveSheet

If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then

xSheet.Range("B11").Copy
Worksheets("All Cases List").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

xSheet.Range("B2").Copy
Worksheets("All Cases List").Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

xSheet.Range("B5:B9").Copy
Worksheets("All Cases List").Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End If

Application.ScreenUpdating = True
End Sub


Thank you