VB to repeat range and create list for each user

[JV0710]

Good Day
Please can I get some help with a macro for the below

I have a sheet with Range A1 : Z16 with Row 1 being headings. The header for column A is “User Name”, column B is “Task” and the balance of the columns relate to various dates and Values and comments .
There are 15 tasks therefore 15 rows Plus 1 for headers.

I have a separate list of 20 users Names

I would like a macro to create list of the 15 Rows for each of the 20 users with each set having a user name from the separate list

Thanks in advance for your help

 

[dermie_72]

you would need to show your data, preferably in an XL2BB. Both the original data and what you would like the data to look like when it's finished.

 

[JV0710]

Hi Dermie

Below is the table I have on Sheet1

User Name	Task	Location	Area	Brand	Group	Category	Check Date	Status	Currency	Cost	Units	Minimum	Maximum	Next Check	Vendor	Schedule	Timing	Colour Code	Comment	Special Instr	Extra 1	Extra 2	Extra 3	Extra 4	Extra 5
	Task 1	Location 1	Area1	Brand a	Grp1	Cat10
	Task 1	Location 2	Area1	Brand a	Grp2	Cat10
	Task 1	Location 3	Area1	Brand a	Grp3	Cat10
	Task 1	Location 4	Area1	Brand a	Grp4	Cat10
	Task 1	Location 5	Area1	Brand a	Grp5	Cat10
	Task 2	Location 1	Area2	Brand b	Grp1	Cat20
	Task 2	Location 2	Area2	Brand b	Grp2	Cat20
	Task 2	Location 3	Area2	Brand b	Grp3	Cat20
	Task 2	Location 4	Area2	Brand b	Grp4	Cat20
	Task 2	Location 5	Area2	Brand b	Grp5	Cat20
	Task 3	Location 1	Area3	Brand c	Grp1	Cat30
	Task 3	Location 2	Area3	Brand c	Grp2	Cat30
	Task 3	Location 3	Area3	Brand c	Grp3	Cat30
	Task 3	Location 4	Area3	Brand c	Grp4	Cat30
	Task 3	Location 5	Area3	Brand c	Grp5	Cat30

Below is the list of User Names I have on Sheet2

User Names
Mary
Jane
Bob
Joe
John
Patrick
Darryll
Lance
Carol
Frank
James
Moe
Audria
Craig
Estelle
Cary
Noel
Sue
Calvin

Below is the result I would like the Macro to produce

User Name	Task	Location	Area	Brand	Group	Category	Check Date	Status	Currency	Cost	Units	Minimum	Maximum	Next Check	Vendor	Schedule	Timing	Colour Code	Comment	Special Instr	Extra 1	Extra 2	Extra 3	Extra 4	Extra 5
Mary	Task 1	Location 1	Area1	Brand a	Grp1	Cat10
Mary	Task 1	Location 2	Area1	Brand a	Grp2	Cat10
Mary	Task 1	Location 3	Area1	Brand a	Grp3	Cat10
Mary	Task 1	Location 4	Area1	Brand a	Grp4	Cat10
Mary	Task 1	Location 5	Area1	Brand a	Grp5	Cat10
Mary	Task 2	Location 1	Area2	Brand b	Grp1	Cat20
Mary	Task 2	Location 2	Area2	Brand b	Grp2	Cat20
Mary	Task 2	Location 3	Area2	Brand b	Grp3	Cat20
Mary	Task 2	Location 4	Area2	Brand b	Grp4	Cat20
Mary	Task 2	Location 5	Area2	Brand b	Grp5	Cat20
Mary	Task 3	Location 1	Area3	Brand c	Grp1	Cat30
Mary	Task 3	Location 2	Area3	Brand c	Grp2	Cat30
Mary	Task 3	Location 3	Area3	Brand c	Grp3	Cat30
Mary	Task 3	Location 4	Area3	Brand c	Grp4	Cat30
Mary	Task 3c	Location 5	Area3	Brand c	Grp5	Cat30
Jane	Task 1	Location 1	Area1	Brand a	Grp1	Cat10
Jane	Task 1	Location 2	Area1	Brand a	Grp2	Cat10
Jane	Task 1	Location 3	Area1	Brand a	Grp3	Cat10
Jane	Task 1	Location 4	Area1	Brand a	Grp4	Cat10
Jane	Task 1	Location 5	Area1	Brand a	Grp5	Cat10
Jane	Task 2	Location 1	Area2	Brand b	Grp1	Cat20
Jane	Task 2	Location 2	Area2	Brand b	Grp2	Cat20
Jane	Task 2	Location 3	Area2	Brand b	Grp3	Cat20
Jane	Task 2	Location 4	Area2	Brand b	Grp4	Cat20
Jane	Task 2	Location 5	Area2	Brand b	Grp5	Cat20
Jane	Task 3	Location 1	Area3	Brand c	Grp1	Cat30
Jane	Task 3	Location 2	Area3	Brand c	Grp2	Cat30
Jane	Task 3	Location 3	Area3	Brand c	Grp3	Cat30
Jane	Task 3	Location 4	Area3	Brand c	Grp4	Cat30
Jane	Task 3c	Location 5	Area3	Brand c	Grp5	Cat30
Bob	Task 1	Location 1	Area1	Brand a	Grp1	Cat10
Bob	Task 1	Location 2	Area1	Brand a	Grp2	Cat10
Bob	Task 1	Location 3	Area1	Brand a	Grp3	Cat10
Bob	Task 1	Location 4	Area1	Brand a	Grp4	Cat10
Bob	Task 1	Location 5	Area1	Brand a	Grp5	Cat10
Bob	Task 2	Location 1	Area2	Brand b	Grp1	Cat20
Bob	Task 2	Location 2	Area2	Brand b	Grp2	Cat20
Bob	Task 2	Location 3	Area2	Brand b	Grp3	Cat20
Bob	Task 2	Location 4	Area2	Brand b	Grp4	Cat20
Bob	Task 2	Location 5	Area2	Brand b	Grp5	Cat20
Bob	Task 3	Location 1	Area3	Brand c	Grp1	Cat30
Bob	Task 3	Location 2	Area3	Brand c	Grp2	Cat30
Bob	Task 3	Location 3	Area3	Brand c	Grp3	Cat30
Bob	Task 3	Location 4	Area3	Brand c	Grp4	Cat30
Bob	Task 3c	Location 5	Area3	Brand c	Grp5	Cat30
Joe	Task 1	Location 1	Area1	Brand a	Grp1	Cat10
Joe	Task 1	Location 2	Area1	Brand a	Grp2	Cat10
Joe	Task 1	Location 3	Area1	Brand a	Grp3	Cat10
Joe	Task 1	Location 4	Area1	Brand a	Grp4	Cat10
Joe	Task 1	Location 5	Area1	Brand a	Grp5	Cat10
Joe	Task 2	Location 1	Area2	Brand b	Grp1	Cat20
Joe	Task 2	Location 2	Area2	Brand b	Grp2	Cat20
Joe	Task 2	Location 3	Area2	Brand b	Grp3	Cat20
Joe	Task 2	Location 4	Area2	Brand b	Grp4	Cat20
Joe	Task 2	Location 5	Area2	Brand b	Grp5	Cat20
Joe	Task 3	Location 1	Area3	Brand c	Grp1	Cat30
Joe	Task 3	Location 2	Area3	Brand c	Grp2	Cat30
Joe	Task 3	Location 3	Area3	Brand c	Grp3	Cat30
Joe	Task 3	Location 4	Area3	Brand c	Grp4	Cat30
Joe	Task 3c	Location 5	Area3	Brand c	Grp5	Cat30
John	Task 1	Location 1	Area1	Brand a	Grp1	Cat10
John	Task 1	Location 2	Area1	Brand a	Grp2	Cat10
John	Task 1	Location 3	Area1	Brand a	Grp3	Cat10
John	Task 1	Location 4	Area1	Brand a	Grp4	Cat10
John	Task 1	Location 5	Area1	Brand a	Grp5	Cat10
John	Task 2	Location 1	Area2	Brand b	Grp1	Cat20
John	Task 2	Location 2	Area2	Brand b	Grp2	Cat20
John	Task 2	Location 3	Area2	Brand b	Grp3	Cat20
John	Task 2	Location 4	Area2	Brand b	Grp4	Cat20
John	Task 2	Location 5	Area2	Brand b	Grp5	Cat20
John	Task 3	Location 1	Area3	Brand c	Grp1	Cat30
John	Task 3	Location 2	Area3	Brand c	Grp2	Cat30
John	Task 3	Location 3	Area3	Brand c	Grp3	Cat30
John	Task 3	Location 4	Area3	Brand c	Grp4	Cat30
John	Task 3c	Location 5	Area3	Brand c	Grp5	Cat30

 

[kvsrinivasamurthy]

Sheet1 --> Tasks
sheet2 --> User names
Sheet3 is Macro result.
Code

Sub UserName_Task()
Dim Tsk As Range, A, T&, Ros&
Application.ScreenUpdating = False
With Sheets("Sheet1").Range("A1").CurrentRegion
Set Tsk = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
Ros = .Rows.Count - 1
End With

A = Sheets("Sheet2").Range("A1").CurrentRegion
With Sheets("Sheet3")
.Range("A1").CurrentRegion.Clear
Sheets("Sheet1").Range("1:1").Copy .Range("A1")
For T = 2 To UBound(A, 1)
Tsk.Copy .Range("A" & 2 + (T - 2) * Ros)            '.Cells(1, (T - 2) * Ros)
.Range("A" & 2 + (T - 2) * Ros).Resize(Ros, 1) = A(T, 1)
Next T
End With
Application.ScreenUpdating = True
End Sub

 

[JV0710]

Sheet1 --> Tasks
sheet2 --> User names
Sheet3 is Macro result.
Code

Sub UserName_Task()
Dim Tsk As Range, A, T&, Ros&
Application.ScreenUpdating = False
With Sheets("Sheet1").Range("A1").CurrentRegion
Set Tsk = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
Ros = .Rows.Count - 1
End With

A = Sheets("Sheet2").Range("A1").CurrentRegion
With Sheets("Sheet3")
.Range("A1").CurrentRegion.Clear
Sheets("Sheet1").Range("1:1").Copy .Range("A1")
For T = 2 To UBound(A, 1)
Tsk.Copy .Range("A" & 2 + (T - 2) * Ros)            '.Cells(1, (T - 2) * Ros)
.Range("A" & 2 + (T - 2) * Ros).Resize(Ros, 1) = A(T, 1)
Next T
End With
Application.ScreenUpdating = True
End Sub

Thanks very much sir . . . That is exactly what was required

 

[kvsrinivasamurthy]

Welcome. Glad that you are happy.