Button click VBA code to insert new row below and include the next number of the task ID.....

[cabellos]

Hi All,

I'm new to this board and would really appreciate some assistance with a macro code.

I am building a task tracker. Each row has a unique task identifier which starts as 0001 and will continue sequentially. I have 1 row entry complete in row 8 that includes other formulas and lists, conditional formatting etc.

At the top of my form (and ideally without having to click in the current row before) i want to be able to 'add new task' whereby a new row with the the next number in the sequence is inserted below and this row includes all the formulas, drop lists and conditional formatting of the row above.

Any help will be very much appreciated.

PN

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[cabellos]

Hi mumps,

Many thanks for your swift reply. I have attached a screenshot which I hope will help with my description. I would like to be able to click the 'Insert New Task' button above and have a new row enter below the last entry in the table and take out the next number. I have formulas, drop down lists and conditional formatting in the cells in each row so all formatting will also need to pull down.

The next row in the list once clicked would appear in cell C9 and would be Task ID 0002, with all other cells blank but the formatting would be copied down.

Thank you very much for your support.

 

[mumps]

You have actually posted a picture rather than a screenshot and it is hard to work with a picture. Please use the XL2BB add-in as mentioned in Post #2 or upload a copy of your file to a file sharing site and post the link here.

 

[cabellos]

Apologies, please follow the dropbox link to view the spreadsheet

Mr Excel File.xlsm

 

[mumps]

Unmerge the cells in the range B1:B8 and try this macro:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B" & LastRow & ":N" & LastRow).Copy Range("B" & LastRow + 1)
    Range("B" & LastRow + 1 & ":N" & LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents
    Range("C" & LastRow + 1) = WorksheetFunction.Max(Range("C8:C" & LastRow + 1)) + 1
    Application.ScreenUpdating = True
End Sub

 

[cabellos]

Fantastic! This is very close! Thank you so much. I am in need of a couple of tweaks if you could support...

I would like the new row to maintain the row height of the first entry. Also, to make this work I actually had to unmerge the cells in the bottom row (row 9). Is there a way to keep this as it is in place as a border? You will also notice the blue cell border is not pulling down on the right most column (column O). Please see link below to file with macro code included and a few clicks to highlight the final edits required.


MY WIP V5 for Mr Excel.xlsm

Many thanks

 

[mumps]

Please unmerge the cells in columns B and O and try this version:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B" & LastRow & ":O" & LastRow).Copy Range("B" & LastRow + 1)
    Rows(LastRow + 1).RowHeight = Rows(LastRow).RowHeight
    Range("B" & LastRow + 1 & ":O" & LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents
    Range("C" & LastRow + 1) = WorksheetFunction.Max(Range("C8:C" & LastRow + 1)) + 1
    Application.ScreenUpdating = True
End Sub

You should avoid merging cells because they almost always cause problems for macros.

 

[vw412]

Here is another routine that should do what you want without messing with your format:

Private Sub CommandButton1_Click()
  Dim LastRow As Range, NewRow As Range, LsstTaskID As Long
  Set LastRow = Range("C" & Rows.Count).End(xlUp).EntireRow
  With LastRow
    LastTaskID = Cells(.Row, 3).Value
    .Offset(1).Insert xlShiftDown, xlFormatFromLeftOrAbove
    .EntireRow.Copy .Offset(1)
    Set NewRow = .Offset(1).EntireRow
  End With
  With NewRow
    .Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents
    Cells(.Row, 3).Value = Right("0000" & (LastTaskID + 1), 5)
    End With
End Sub

Note this code assumes the TaskID will always be in column C.

 

[cabellos]

Fantastic!!! I really appreciate your help with this. It works a treat!