Repeating data using column contents in to rows

LeighMacKay7 · Sep 15, 2023

Hi, hoping somebody can offer me some advice/solution.
I have received an excel workbook, which contains 24 columns of data.
However, columns C to M contain different names, under columns called, Project Lead, Co-Investigator 1, Co-Investigator 2, and so forth (all the way up to Co-Investigator 9).
I'd like to repeat the data in columns A, B, N to X. But have a new row for each of the Project Leads & Co-Investigators.
Is this possible?

Joe4 · Sep 15, 2023

Absolutely.

Here is a little procedure that will copy the values from columns, A, B, N, and X down to the next row to start your new record entry:

VBA Code:

Sub MyCopyColumns()

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy values in columns A, B, N, and X to new row
    Range(Cells(lr, "A"), Cells(lr, "B")).Copy Cells(lr + 1, "A")
    Cells(lr, "N").Copy Cells(lr + 1, "N")
    Cells(lr, "X").Copy Cells(lr + 1, "X")
    
End Sub

LeighMacKay7 · Sep 15, 2023

Joe4 said:
Absolutely.

Here is a little procedure that will copy the values from columns, A, B, N, and X down to the next row to start your new record entry:

VBA Code:

Sub MyCopyColumns() Dim lr As Long ' Find last row in column A with data lr = Cells(Rows.Count, "A").End(xlUp).Row ' Copy values in columns A, B, N, and X to new row Range(Cells(lr, "A"), Cells(lr, "B")).Copy Cells(lr + 1, "A") Cells(lr, "N").Copy Cells(lr + 1, "N") Cells(lr, "X").Copy Cells(lr + 1, "X") End Sub

Thank you for your VBA code Joe.

I have changed my file to a .xlsm workbook.

And I have added the code. Developer > Visual Basic > Module1 .
Then pasted in the VBA code.

How do I run it?

I have selected the appropriate tab. And clicked Developer > Macros > Selected Macro Name: MyCopyColumns > Run.

And it hasn't worked.

Any advice / helpful tips / videos, would be appreciated.

Thanks in advance.

Joe4 · Sep 15, 2023

That is exactly how you would do it.

What exactly is in columns A, B, X, and N?
Are they formulas or hard-coded values?

Can you post a sample of your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

LeighMacKay7 · Sep 15, 2023

Joe4 said:
That is exactly how you would do it.

What exactly is in columns A, B, X, and N?
Are they formulas or hard-coded values?

Can you post a sample of your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Example Workbook - repeating data using column contents in to rows.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

Project ID

Project Title

Project Lead

Co-Investigator(s@lincoln.ac.uk)

Co-Investigator(s@lincoln.ac.uk)2

Co-Investigator(s@lincoln.ac.uk)3

Co-Investigator(s@lincoln.ac.uk)4

Co-Investigator(s@lincoln.ac.uk)5

Co-Investigator(s@lincoln.ac.uk)6

Co-Investigator(s@lincoln.ac.uk)7

Co-Investigator(s@lincoln.ac.uk)8

Co-Investigator(s@lincoln.ac.uk)9

Co-Investigator(s@lincoln.ac.uk)10

Lead Org Unit

Other Org Units

Status

Bid Submitted Date

Bid Awarded Date

Project Dates Start

Project Dates End

Funder

Price to Funder

Price to Funder (ex Partner Costs)

Discipline

2

8511592

Porject Title 1

Xabi Alonso (Xalonso@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Virgil van Dijk (VVDijk@testdata.co.uk)

Luis Suarez (Lsuarez@testdata.co.uk)

Lead Org Unit 1

Department of Athletics

Award Acceptance

2022-12-15 09:03

2023-01-23

2023-03-01

2023-07-31

Funder 6

14975.09

12975.09

3

8667218

Porject Title 2

Mo Salah (Msalah@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Xabi Alonso (Xalonso@testdata.co.uk)

Lead Org Unit 3

Department of Football

Award Setup

2023-03-21 23:10

2023-07-10

2024-03-21

2025-09-20

Funder 2

123430.2

4

8765364

Porject Title 3

Leigh MacK (Lmack@testdata.co.uk)

Luiz Diaz (Ldiaz@testdata.co.uk)

Virgil van Dijk (VVDijk@testdata.co.uk)

Cody Gakpo (Cgakpo@testdata.co.uk)

Leigh MacK (Lmack@testdata.co.uk)

J Mascherano (Jmascherano@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Luis Suarez (Lsuarez@testdata.co.uk)

Stevie G (Sgerrard@testdata.co.uk_

Mo Salah (Msalah@testdata.co.uk)

Lead Org Unit 4

Department of Golf

Bid Awaiting Response

2023-06-12 11:58

2023-06-19

2023-09-30

Funder 5

29700

5

8763099

Porject Title 4

Mo Salah (Msalah@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Stevie G (Sgerrard@testdata.co.uk_

Xabi Alonso (Xalonso@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Virgil van Dijk (VVDijk@testdata.co.uk)

Lead Org Unit 5

Department of Management

Bid Awaiting Response

2023-06-12 10:19

2023-08-01

2024-07-31

Funder 1

7500

4800

6

8327612

Porject Title 5

Fernando Torres (Ftorres@testdata.co.uk)

J Mascherano (Jmascherano@testdata.co.uk)

Lead Org Unit 1

Department of Swimming

Bid Awaiting Response

2023-06-12 10:04

2023-01-24

2024-01-23

Funder 6

20000.01

10000.01

7

8598109

Porject Title 6

Virgil van Dijk (VVDijk@testdata.co.uk)

Luiz Diaz (Ldiaz@testdata.co.uk)

Mo Salah (Msalah@testdata.co.uk)

Leigh MacK (Lmack@testdata.co.uk)

Luis Suarez (Lsuarez@testdata.co.uk)

Lead Org Unit 1

Department of Football

Bid Awaiting Response

2023-06-09 13:05

2024-08-01

2026-01-31

Funder 4

120306.27

8

8512247

Porject Title 7

Xabi Alonso (Xalonso@testdata.co.uk)

Lead Org Unit 1

Department of Management

Bid Awaiting Response

2023-06-07 16:29

2023-11-01

2025-10-31

Funder 9

6748.95

9

8762504

Porject Title 8

J Mascherano (Jmascherano@testdata.co.uk)

Luiz Diaz (Ldiaz@testdata.co.uk)

Lead Org Unit 9

Department of Football

Bid Awaiting Response

2023-05-30 14:32

2023-09-01

2025-08-31

British Academy

8560

10

8720706

Porject Title 9

Leigh MacK (Lmack@testdata.co.uk)

Stevie G (Sgerrard@testdata.co.uk)

Xabi Alonso (Xalonso@testdata.co.uk)

Leigh MacK (Lmack@testdata.co.uk)

J Mascherano (Jmascherano@testdata.co.uk)

Mo Salah (Msalah@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Leigh MacK (Lmack@testdata.co.uk)

Lead Org Unit 8

Department of Defending

Bid Awaiting Response

2023-05-26 17:28

2023-10-02

2024-12-31

British Academy

5691.75

11

8759066

Porject Title 10

Mo Salah (Msalah@testdata.co.uk)

Samy Amer (samer@testdata.co.uk)

Stevie G (Sgerrard@testdata.co.uk)

Fernando Torres (Ftorres@testdata.co.uk)

Virgil van Dijk (VVDijk@testdata.co.uk)

Lead Org Unit 5

Department of Attacking

Bid Awaiting Response

2023-05-26 15:08

2023-09-01

2025-04-30

British Academy

8769.75

Example dataset

LeighMacKay7 · Sep 15, 2023

Sorry, was downloading the XI2bb and creating the mini sheet so I could show my example data. Which I think has now been posted above.

Joe4 · Sep 15, 2023

Are you working with data tables? It looks like you might.

I am also confused. Tou said that you want repeat the values in columns A, B, N, and X, but if I look at the values in those columns, they are not repeating. They are all different.
So I am not really clear on exactly what it is you are trying to do.

Can you walk us through an example, based on your sample data above?
Tell us what you are trying to do, and exactly what should appear in what cells in your example.

LeighMacKay7 · Sep 15, 2023

What I would expect to see.

Example Workbook - repeating data using column contents in to rows.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

Project ID

Project Title

Project Lead

Co-Investigator(s@lincoln.ac.uk)

Co-Investigator(s@lincoln.ac.uk)2

Co-Investigator(s@lincoln.ac.uk)3

Co-Investigator(s@lincoln.ac.uk)4

Co-Investigator(s@lincoln.ac.uk)5

Co-Investigator(s@lincoln.ac.uk)6

Co-Investigator(s@lincoln.ac.uk)7

Co-Investigator(s@lincoln.ac.uk)8

Co-Investigator(s@lincoln.ac.uk)9

Co-Investigator(s@lincoln.ac.uk)10

Lead Org Unit

Other Org Units

Status

Bid Submitted Date

Bid Awarded Date

Project Dates Start

Project Dates End

Funder

Price to Funder

Price to Funder (ex Partner Costs)

Discipline

2

8511592

Porject Title 1

Xabi Alonso (Xalonso@testdata.co.uk)

Lead Org Unit 1

Department of Athletics

Award Acceptance

2022-12-15 09:03

2023-01-23

2023-03-01

2023-07-31

Funder 6

14975.09

12975.09

3

8511592

Porject Title 1

Fernando Torres (Ftorres@testdata.co.uk)

Lead Org Unit 1

Department of Athletics

Award Acceptance

2022-12-15 09:03

2023-01-23

2023-03-01

2023-07-31

Funder 6

14975.09

12975.09

4

8511592

Porject Title 1

Virgil van Dijk (VVDijk@testdata.co.uk)

Lead Org Unit 1

Department of Athletics

Award Acceptance

2022-12-15 09:03

2023-01-23

2023-03-01

2023-07-31

Funder 6

14975.09

12975.09

5

8511592

Porject Title 1

Luis Suarez (Lsuarez@testdata.co.uk)

Lead Org Unit 1

Department of Athletics

Award Acceptance

2022-12-15 09:03

2023-01-23

2023-03-01

2023-07-31

Funder 6

14975.09

12975.09

6

8667218

Porject Title 2

Mo Salah (Msalah@testdata.co.uk)

Lead Org Unit 3

Department of Football

Award Setup

2023-03-21 23:10

2023-07-10

2024-03-21

2025-09-20

Funder 2

123430.2

7

8667218

Porject Title 2

Fernando Torres (Ftorres@testdata.co.uk)

Lead Org Unit 3

Department of Football

Award Setup

2023-03-21 23:10

2023-07-10

2024-03-21

2025-09-20

Funder 2

123430.2

8

8667218

Porject Title 2

Xabi Alonso (Xalonso@testdata.co.uk)

Lead Org Unit 3

Department of Football

Award Setup

2023-03-21 23:10

2023-07-10

2024-03-21

2025-09-20

Funder 2

123430.2

9

8765364

Porject Title 3

Leigh MacK (Lmack@testdata.co.uk)

Lead Org Unit 4

Department of Golf

Bid Awaiting Response

2023-06-12 11:58

2023-06-19

2023-09-30

Funder 5

29700

10

8765364

Porject Title 3

Luiz Diaz (Ldiaz@testdata.co.uk)

Lead Org Unit 4

Department of Golf

Bid Awaiting Response

2023-06-12 11:58

2023-06-19

2023-09-30

Funder 5

29700

11

8765364

Porject Title 3

Virgil van Dijk (VVDijk@testdata.co.uk)

Lead Org Unit 4

Department of Golf

Bid Awaiting Response

2023-06-12 11:58

2023-06-19

2023-09-30

Funder 5

29700

12

8765364

Porject Title 3

Cody Gakpo (Cgakpo@testdata.co.uk)

Lead Org Unit 4

Department of Golf

Bid Awaiting Response

2023-06-12 11:58

2023-06-19

2023-09-30

Funder 5

29700

Expected Results

Joe4 · Sep 15, 2023

You haven't answered the first question from my previous reply.

Are you working with data tables?
If so, we will probably need to insert a new row into the table before copying the data down.
So this is an EXTERMELY important question we need answered.
Please also let us know the name of the table.

Also, do you have anything at all in column A after cell A11 (your last row of data)?

Joe4 · Sep 15, 2023

If you do indeed have a table, this code should do what you want:

VBA Code:

Sub AddRowToTable()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lrow As Long

    Set ws = ActiveSheet
    Set tbl = ws.ListObjects("Table1")

'   Find last row in table with data
    lrow = Range("A1").End(xlDown).Row

'   Add new row to bottom of table
    tbl.ListRows.Add
    
'   Copy values from last row to new row
    Cells(lr + 1, "A").Value = Cells(lr, "A").Value
    Cells(lr + 1, "B").Value = Cells(lr, "B").Value
    Cells(lr + 1, "N").Value = Cells(lr, "N").Value
    Cells(lr + 1, "X").Value = Cells(lr, "X").Value

End Sub

Just substitute "Table1" in the code with the actual name of your table.

Repeating data using column contents in to rows

LeighMacKay7

New Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

LeighMacKay7

New Member

Joe4

MrExcel MVP, Junior Admin

LeighMacKay7

New Member

LeighMacKay7

New Member

Joe4

MrExcel MVP, Junior Admin

LeighMacKay7

New Member

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

Repeating data using column contents in to rows

New Member

Excel Facts

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

New Member

New Member

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock