Repeating data using column contents in to rows

L

LeighMacKay7

New Member
Joined
Oct 11, 2022
Messages
34
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Web
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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
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
 
Upvote 0
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
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...
Example Workbook - repeating data using column contents in to rows.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1Project IDProject TitleProject LeadCo-Investigator(s@lincoln.ac.uk)Co-Investigator(s@lincoln.ac.uk)2Co-Investigator(s@lincoln.ac.uk)3Co-Investigator(s@lincoln.ac.uk)4Co-Investigator(s@lincoln.ac.uk)5Co-Investigator(s@lincoln.ac.uk)6Co-Investigator(s@lincoln.ac.uk)7Co-Investigator(s@lincoln.ac.uk)8Co-Investigator(s@lincoln.ac.uk)9Co-Investigator(s@lincoln.ac.uk)10Lead Org UnitOther Org UnitsStatusBid Submitted DateBid Awarded DateProject Dates StartProject Dates EndFunderPrice to FunderPrice to Funder (ex Partner Costs)Discipline
28511592Porject Title 1Xabi 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 1Department of AthleticsAward Acceptance2022-12-15 09:032023-01-232023-03-012023-07-31Funder 614975.0912975.09
38667218Porject Title 2Mo Salah (Msalah@testdata.co.uk)Fernando Torres (Ftorres@testdata.co.uk)Xabi Alonso (Xalonso@testdata.co.uk)Lead Org Unit 3Department of FootballAward Setup2023-03-21 23:102023-07-102024-03-212025-09-20Funder 2123430.2123430.2
48765364Porject Title 3Leigh 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 4Department of GolfBid Awaiting Response2023-06-12 11:582023-06-192023-09-30Funder 52970029700
58763099Porject Title 4Mo 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 5Department of ManagementBid Awaiting Response2023-06-12 10:192023-08-012024-07-31Funder 175004800
68327612Porject Title 5Fernando Torres (Ftorres@testdata.co.uk)J Mascherano (Jmascherano@testdata.co.uk)Lead Org Unit 1Department of SwimmingBid Awaiting Response2023-06-12 10:042023-01-242024-01-23Funder 620000.0110000.01
78598109Porject Title 6Virgil 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 1Department of FootballBid Awaiting Response2023-06-09 13:052024-08-012026-01-31Funder 4120306.27120306.27
88512247Porject Title 7Xabi Alonso (Xalonso@testdata.co.uk)Lead Org Unit 1Department of ManagementBid Awaiting Response2023-06-07 16:292023-11-012025-10-31Funder 96748.956748.95
98762504Porject Title 8J Mascherano (Jmascherano@testdata.co.uk)Luiz Diaz (Ldiaz@testdata.co.uk)Lead Org Unit 9Department of FootballBid Awaiting Response2023-05-30 14:322023-09-012025-08-31British Academy85608560
108720706Porject Title 9Leigh 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 8Department of DefendingBid Awaiting Response2023-05-26 17:282023-10-022024-12-31British Academy5691.755691.75
118759066Porject Title 10Mo 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 5Department of AttackingBid Awaiting Response2023-05-26 15:082023-09-012025-04-30British Academy8769.758769.75
Example dataset
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
What I would expect to see.

Example Workbook - repeating data using column contents in to rows.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1Project IDProject TitleProject LeadCo-Investigator(s@lincoln.ac.uk)Co-Investigator(s@lincoln.ac.uk)2Co-Investigator(s@lincoln.ac.uk)3Co-Investigator(s@lincoln.ac.uk)4Co-Investigator(s@lincoln.ac.uk)5Co-Investigator(s@lincoln.ac.uk)6Co-Investigator(s@lincoln.ac.uk)7Co-Investigator(s@lincoln.ac.uk)8Co-Investigator(s@lincoln.ac.uk)9Co-Investigator(s@lincoln.ac.uk)10Lead Org UnitOther Org UnitsStatusBid Submitted DateBid Awarded DateProject Dates StartProject Dates EndFunderPrice to FunderPrice to Funder (ex Partner Costs)Discipline
28511592Porject Title 1Xabi Alonso (Xalonso@testdata.co.uk)Lead Org Unit 1Department of AthleticsAward Acceptance2022-12-15 09:032023-01-232023-03-012023-07-31Funder 614975.0912975.09
38511592Porject Title 1Fernando Torres (Ftorres@testdata.co.uk)Lead Org Unit 1Department of AthleticsAward Acceptance2022-12-15 09:032023-01-232023-03-012023-07-31Funder 614975.0912975.09
48511592Porject Title 1Virgil van Dijk (VVDijk@testdata.co.uk)Lead Org Unit 1Department of AthleticsAward Acceptance2022-12-15 09:032023-01-232023-03-012023-07-31Funder 614975.0912975.09
58511592Porject Title 1Luis Suarez (Lsuarez@testdata.co.uk)Lead Org Unit 1Department of AthleticsAward Acceptance2022-12-15 09:032023-01-232023-03-012023-07-31Funder 614975.0912975.09
68667218Porject Title 2Mo Salah (Msalah@testdata.co.uk)Lead Org Unit 3Department of FootballAward Setup2023-03-21 23:102023-07-102024-03-212025-09-20Funder 2123430.2123430.2
78667218Porject Title 2Fernando Torres (Ftorres@testdata.co.uk)Lead Org Unit 3Department of FootballAward Setup2023-03-21 23:102023-07-102024-03-212025-09-20Funder 2123430.2123430.2
88667218Porject Title 2Xabi Alonso (Xalonso@testdata.co.uk)Lead Org Unit 3Department of FootballAward Setup2023-03-21 23:102023-07-102024-03-212025-09-20Funder 2123430.2123430.2
98765364Porject Title 3Leigh MacK (Lmack@testdata.co.uk)Lead Org Unit 4Department of GolfBid Awaiting Response2023-06-12 11:582023-06-192023-09-30Funder 52970029700
108765364Porject Title 3Luiz Diaz (Ldiaz@testdata.co.uk)Lead Org Unit 4Department of GolfBid Awaiting Response2023-06-12 11:582023-06-192023-09-30Funder 52970029700
118765364Porject Title 3Virgil van Dijk (VVDijk@testdata.co.uk)Lead Org Unit 4Department of GolfBid Awaiting Response2023-06-12 11:582023-06-192023-09-30Funder 52970029700
128765364Porject Title 3Cody Gakpo (Cgakpo@testdata.co.uk)Lead Org Unit 4Department of GolfBid Awaiting Response2023-06-12 11:582023-06-192023-09-30Funder 52970029700
Expected Results
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
What Is Best Practice To Facilitate Column Layout Change In A Project?
Replies
2
Views
205
ozex
O
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
788
Micron
M
M
  • Solved
Is a PivotTable with VBA the best way to dynamically add/remove columns and rows ?
Replies
8
Views
304
MasterBash
M
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
55
Peter_SSs
Peter_SSs
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
110
Denny57
D

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top