Copy entire row in insert in the middle of a table.

Hello,

I am having some issues with the attached file that I was hoping someone could help me with. You will see the file has four tenant types. I want to have a button for each type that says insert "type name". The code will highlight an entire row and insert it at the last blank row in that specific type section. For example. The types are Restaurants, Variety Stores, Outparcels and Anchors. If I want to add a Variety Store. I would click the button. It would copy a variety store row that is hidden and insert it in the variety store section of the table. I was messing around with the code below but it isn't working.

Sub AddVariety()
    Range("113:113").Select
    Selection.Copy
    Range("E19" & Rows.Count).End(xlUp).Select
    Selection.Insert Shift:=xlDown
    
End Sub

Any help would be greatly appreciated. Thanks!

I think instead of Range("E19" & Rows.count) you probably want Range("E" & Rows.count). I think you might also want to insert it in the row after so you would need Range("E" & Rows.count).End(xlUp).Offset(1,0).Select

Try something like:

Sub AddVariety()
    Rows(113).copy
    Range("E19" & Rows.Count).End(xlUp).Offset(1,0).Insert Shift:=xlDown
End Sub

Thanks yudlugar. I get a debug when I run the code on the Range ("E19" & Rows.Count).End(xlUp).Offset(1,0).Insert Shift:=xlDown line of code.

Ha. I told you what to change then didn't do it. Delete the 19 part.

Not sure how it works in later versions of excel but I have a feeling that you may also need to select the same amount of columns for what you are trying to insert as to what you copy. So if you copy the entire row, you need to select an entire row to insert it into. So you probably need:

Sub AddVariety()
    Rows(113).copy
    Rows(Range("E" & Rows.Count).End(xlUp).row+1).Insert Shift:=xlDown
End Sub

Rows(113).copy
    Rows(Range("E" & Rows.Count).End(xlUp).row+1).Insert Shift:=xlDown

Can not copy an entire row to column E, should be column A.

If you look at the file attached you will see the different sections. I want the user to be able to insert a row in within the different sections when they want to add a row for variety store or restaurant etc.

AB33, Agreed, I wasn't entirely sure what OP is trying to copy and where it should look, but I was working on it being the last row in column E being the one where the new row should be inserted. I think my code should work though because I'm using Rows(Range("E".... not Range(Range("E"...

the code is working now but it isn't inserting the row in that specific section of the tables. It is inserting them after rows 113. I need it to copy the row 113 which I am going to move to hidden rows above once the code is working. Then go to the variety store section find the last blank row and insert it there (It would insert at row 18 in this case. Then if I ran it a second time it would insert at row 19 and so on. Right now it is inserting them in row 117.

ok

So you probably want something like this.

Sub AddVariety()
    Rows(113).copy
    Rows(Range("E112").End(xlUp).row+1).Insert Shift:=xlDown
End Sub

The problem now is that row 113 will change everytime you insert it and shift it down? You might be better copying instead?

This is what I ended up with on the code

Sub AddVariety()
    Worksheets("RE Tax Cntrb Sched").Activate
    Rows(2).Copy
    Rows(Range("VarietyStoreContrib").End(xlUp).Row + 1).Insert Shift:=xlDown
End Sub

Thanks again. I did a named range instead of the cell reference because every time I inserted a row that cell reference would have needed to change and a direct cell reference in the code wouldn't allow for that.

SAWEET!! That worked. Thanks yudlugar! what does yudlugar mean?

No problem, sorry it took a while. It has no meaning. It's a word I made up playing scrabble.