ocu2fan
06-21-2019, 03:56 PM
Thank you in advance for your help with this. I'm not any sort of expert in Excel, but my boss has tasked me with updating a log that we use for tracking boxes pulled for onsite research. We are rolling out a new system beginning July 1, so the workbook is currently formatted for the months of July (Sheet 11) and August (Sheet 12) only.
First off we use Excel 2016. The workbook contains 13 visible sheets (Recap followed by 1 sheet for each month of the year).
There are 8 visible columns, but it's the first two that are important (Tracking Number and Date).
I was able to find a code that would generate a new 'Tracking Number' when the 'Date' is filled in:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxNumber
If Not Intersect(Target, Range("B:B")) Is Nothing Then
' don't run when more than one row is changed
If Target.Rows.Count > 1 Then Exit Sub
' if column A in the current row has a value, don't run
If Cells(Target.Row, 1) > 0 Then Exit Sub
' get the highest number in column A, then add 1 and write to the
' current row, column A
maxNumber = Application.WorksheetFunction.Max(Range("A:A"))
Target.Offset(0, -1) = maxNumber + 1
End If
End Sub
This is the easiest part since all I needed was to have the two digit Fiscal Year (19) followed by a sequential number: (Worksheet July A2) 19-1.
This would be fine if we were using just one worksheet, but since the workbook is broken down by month my boss would like the numbering to continue on each subsequent month. So if in the month of July we begin with 19-1 and end with 19-20, August's first tracking number should be 19-21 and so on.
I can't find anything online that would make it possible for the 'Tracking Number' to continue on the next sheet with a 'Tracking Number' that would be the last tracking number in July +1.
Like I said, we are starting this on July 1. Our Fiscal Year ends September 30, so I would need to be able to replicate the code into a new workbook for next Fiscal Year beginning October 1.
I really hope this makes sense. I am not a VBA expert and I would barely call myself a novice. I can't seem to find anything online that is close to what I need.
My apologies in advance, if I didn't provide enough specificity. Thank you again for your assistance.
First off we use Excel 2016. The workbook contains 13 visible sheets (Recap followed by 1 sheet for each month of the year).
There are 8 visible columns, but it's the first two that are important (Tracking Number and Date).
I was able to find a code that would generate a new 'Tracking Number' when the 'Date' is filled in:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxNumber
If Not Intersect(Target, Range("B:B")) Is Nothing Then
' don't run when more than one row is changed
If Target.Rows.Count > 1 Then Exit Sub
' if column A in the current row has a value, don't run
If Cells(Target.Row, 1) > 0 Then Exit Sub
' get the highest number in column A, then add 1 and write to the
' current row, column A
maxNumber = Application.WorksheetFunction.Max(Range("A:A"))
Target.Offset(0, -1) = maxNumber + 1
End If
End Sub
This is the easiest part since all I needed was to have the two digit Fiscal Year (19) followed by a sequential number: (Worksheet July A2) 19-1.
This would be fine if we were using just one worksheet, but since the workbook is broken down by month my boss would like the numbering to continue on each subsequent month. So if in the month of July we begin with 19-1 and end with 19-20, August's first tracking number should be 19-21 and so on.
I can't find anything online that would make it possible for the 'Tracking Number' to continue on the next sheet with a 'Tracking Number' that would be the last tracking number in July +1.
Like I said, we are starting this on July 1. Our Fiscal Year ends September 30, so I would need to be able to replicate the code into a new workbook for next Fiscal Year beginning October 1.
I really hope this makes sense. I am not a VBA expert and I would barely call myself a novice. I can't seem to find anything online that is close to what I need.
My apologies in advance, if I didn't provide enough specificity. Thank you again for your assistance.