Repeating data using column contents in to rows

[LeighMacKay7]

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?

 

[LeighMacKay7]

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)?

Apologies.

No, not working with data tables. Based on my understanding.

And yes, the data goes from range A1 to X90 on the real data sheet.

My example dataset range is A1 to X11

 

[Joe4]

I see you marked my first post as the solution.
Does that mean you got it to work, or are you still having issues?

 

[LeighMacKay7]

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

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.

I have changed my data to a table on the back of this and called it "RES.tbl" To which I have changed the text.

I am getting debug error.

 

[Joe4]

OK. Please do NOT mark a post as the solution, if the problem is not solved!
That is a bit misleading and confusing.

What is the exact error message you are getting?
If you hover over the "lr" variable in your code when you get this error, what does it show as its value?