Insert Row at Bottom of Table Maintain Formulas and Format Using VBA Button

[gplans]

I would like to know how you can use a macro to insert a row at the very bottom of your table and also maintain the formatting and formulas from the table.

I used this code but it does not copy the formatting or the formulas from the line above.

I use this technique from the link in this video.

https://www.youtube.com/watch?v=9JTLJ1XjOJc

Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.EntireRow.Insert

Any help Thanks

 

[gplans]

Yessss!! Thanks it worked!!

 

[gplans]

Yessss!! Thanks it worked!!

If I wanted to create the same lines with the same button press but on another sheet do I double up the code but rename ti the sheet in the code. Like this?



Private Sub CommandButton1_Click()


Dim myRow As Long, tblRow As ListRow
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBS Main Data").ListObjects("Table5").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
With Range("A" & myRow)
.Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1
.HorizontalAlignment = xlCenter
End With




Dim myRow As Long, tblRow As ListRow
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBSMAINT Job Cost Download").ListObjects("Table5").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
With Range("A" & myRow)
.Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1
.HorizontalAlignment = xlCenter
End With

 

[Michael M]

You can, but you have made it a little harder by not having a complete table in the Job Cost Sheet !
I also note, unless you have changed the sheet names, the one you are using for the MAINT sheet is incorrect....so double check you sheet names !!

Private Sub CommandButton1_Click()
Dim myRow As Long, tblRow As ListRow
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBS Main Data").ListObjects("Table5").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
    With Range("A" & myRow)
        .Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1
        .HorizontalAlignment = xlCenter
    End With
Sheets("OBS Job Cost Download").Activate
myRow = Sheets("OBS Job Cost Download").Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBS Job Cost Download").ListObjects("Table4").ListRows.Add(myRow - 17)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
    With Sheets("OBS Job Cost Download").Range("A" & myRow - 15)
        .Value = "OBSM" & Right(Range("A" & myRow - 16).Value, 6) + 1
        .HorizontalAlignment = xlCenter
    End With
End Sub

 

[gplans]

Thanks Michael,

The code runs good until it hits 'Sheets("OBS Job Cost Download").Activate"

I then get a Run-time error '0': Subscript out of range

I had a look at this error and it mentions that the sheet is not named correctly, but it is.

 

[Michael M]

Check for leading or trailling spaces in the sheet name AND the names in the code !!

 

[gplans]

Ok I changed the name but now it stops the Run-time error.

It now stops on the lines below with the same run-time error.

With Sheets("OBS Job Cost Download").Range("A" & myRow - 15)
.Value = "OBSM" & Right(Range("A" & myRow - 16).Value, 6) + 1



Why is the line not the same as above.

With Range("A" & myRow)
.Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1

I changed it and no error but it does not change the number or center the OBSM100045 either.

 

[Michael M]

Because you have about 13 unused rows in the table
If you delete the blank rows in the OBS Job data table, except for the "Grand Total" row, this should do the trick

Private Sub CommandButton1_Click()
Dim myRow As Long, tblRow As ListRow
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBS Main Data").ListObjects("Table5").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
    With Range("A" & myRow)
        .Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1
        .HorizontalAlignment = xlCenter
    End With
Sheets("OBS Job Cost Download").Activate
myRow = Sheets("OBS Job Cost Download").Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBS Job Cost Download").ListObjects("Table4").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
    With Sheets("OBS Job Cost Download").Range("A" & myRow)
        .Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1
        .HorizontalAlignment = xlCenter
    End With
End Sub

 

[gplans]

Ok it is somewhat working.

The number came out as follows:

OBS Main Data = OBSMAINT Job Cost Download

OBSM100010 =	OBSM100010
OBSM100011 =	OBSM100011
OBSM100016 =	OBSM100016
OBSM100013 =	OBSM100013
OBSM100012 =	OBSM100012
OBSM100013 =	OBSM100013

<tbody>
</tbody>

Not sure why.

 

[Michael M]

Did you remove the unused rows on the Job Cost sheet ?
Is the Main data sheet the activesheet when you run the code ?
Is the code in a Regular module ?
It works for me except, this 3rd last line needs to be

.HorizontalAlignment = xlCenter

.HorizontalAlignment = xlLeft

AND
The data isn't filtered is it !!

 

[gplans]

"Private Sub CommandButton1_Click()


Dim myRow As Long, tblRow As ListRow
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBS Main Data").ListObjects("Table5").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
With Range("A" & myRow)
.Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1
.HorizontalAlignment = xlCenter
End With
Sheets("OBSMAINT Job Cost Download").Activate
myRow = Sheets("OBSMAINT Job Cost Download").Cells(Rows.Count, "A").End(xlUp).Row
Set tblRow = Sheets("OBSMAINT Job Cost Download").ListObjects("Table4").ListRows.Add(myRow - 2)
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
With Sheets("OBSMAINT Job Cost Download").Range("A" & myRow)

.HorizontalAlignment = xlCenter
End With


Sheets("OBS Main Data").Activate


End Sub"

Not sure what you meant by:

Did you remove the unused rows on the Job Cost sheet ? (Where I cant see any unused rows)
Is the Main data sheet the activesheet when you run the code ? (Yes I am on OBS Main Data when I run the code)
Is the code in a Regular module ? (Yes I think I am not Debugging)
It works for me except, this 3rd last line needs to be (I got it to work by taking out Value = "OBSM" & Right(Range("A" & myRow).Value, 6) + 1 line and getting it to link back to OBS Main Data) Not ideals as I could not get the original intention to work.

I tried to Debug but I do not know enough to know what I am look at. Where are the 13 unused rows? Do you have a no content version that I put on dropbox?