Hi, I am learning to utilize vba for our tracking log at work. Since multiple people will be updating this log, I wanted to utilize vba instead of formulas just in case it gets messed with.
This log is a tracking log for all the requests that we receive. The main sheet has a code that will auto-fill column A with a unique tracking number after a command button is clicked. But I also have a Worksheet_change sub that will automatically remove the entire row if a completed date is entered in column T and it pastes into a different "Completed" sheet. When this happens, the tracking numbers for all rows after the "completed" row will update. I need the tracking number to not update when rows are removed because all rows inputted into the main sheet are copied into other sheets depending on what type of request it is. We also give the tracking number to the requestor.
I've seen formulas using MAX to find the highest number in the column and then to increase it by one, but again, I want it to be automatic through vba as some of my coworkers do not know excel and want it to be as simple as possible. I don't' know if I'm just not searching with the right tags, but I can't seem to find anything that can help me. I know I could just already have the numbers pre-filled all the way down to row n but I want to try to keep it as clean as possible.
The code I have now is:
Sub FillSerialNumbers()
With Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Cells(1, 1).Value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With
End Sub
Being new to macros, it's taken me a week to finally get the log working the way I want, and while it may look crude to the experienced (sorry..), this is the last bump that I can't overcome. Any and all help is appreciated. Thank you!
Bookmarks