VBA to copy cell value to another column with dynamic rows

[bogdanxd]

Hello people,

I'm running to an issue here with a table.
I need to build another column, lets say "Column 13", and for each row I need to fill a cell with the value from Column 1 as shown below.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Column 11	Column 12
2	Done by: Name Firstname
3
4	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
5	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
6	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
7	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
8	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
9	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
10	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
11	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
12	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
13	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
14	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
15	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
16	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
17	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
18	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
19	Done by: Name Firstname
20
21
22	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
23	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
24	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
25	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
26	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
27	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
28	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
29	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
30	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
31	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
32	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
33	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
34	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
35	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
36	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
37	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata
Sheet2

And so on if I have more "Done by" entries.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Column 11	Column 12	Column 13
2	Done by: Name Firstname
3
4	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
5	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
6	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
7	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
8	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
9	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
10	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
11	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
12	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
13	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
14	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
15	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
16	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
17	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
18	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname
19	Done by: Name Firstname 2
20
21
22	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
23	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
24	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
25	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
26	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
27	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
28	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
29	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
30	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
31	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
32	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
33	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
34	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
35	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
36	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
37	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	somedata	Done by: Name Firstname 2
Sheet2

Cell Formulas
Range	Formula
M22:M37	M22	=$A$19

Thanks in advance guys !

 

[shknbk2]

Try this in Column 13, row 2:

=IF(B2="","",IF(M1<>"",M1,LOOKUP(2,1/($A1:A$2<>""),$A1:A$2)))

The table should fill in the rest of column 13.

 

[Marc L]

Hi, according to the attachment a VBA demonstration as a beginner starter :​

Sub Demo1()
         Const C = "Done by:*"
           Dim Rg(1) As Range, R&
    With ActiveSheet.UsedRange.Columns(1)
           Set Rg(0) = .Find(C, , xlValues, , , 2)
        If Not Rg(0) Is Nothing Then
                R = Rg(0).Row
            Do
                Set Rg(1) = Rg(0).End(xlDown).CurrentRegion
                If Rg(1)(Rg(1).Rows.Count, 1).Text Like C Then Set Rg(1) = Rg(1).Resize(Rg(1).Rows.Count - 1)
                Rg(1).Columns(13).Value2 = Rg(0).Text
                Set Rg(0) = .FindPrevious(Rg(0))
            Loop Until Rg(0).Row = R
                Erase Rg
                [M1].Value2 = "Column 13"
               .Columns(13).AutoFit
        End If
    End With
End Sub

 

[bogdanxd]

Thank you so much to both of you !