autonumbering for multiple columns based on two cells
Hi
I want autonumbering and increment in D6 based on match cell D2 with headers in G1:J1 , if they're matched , then should increment number in D6 based on the last row for cell in columns G or H or I or J depends on matching with D2 and after autonumbering and increment in D6 then should copy under last row for cell in column match header with D2 as I highlighted in CR1,CR2,CR2 sheets (theses just examples )
I put example when use SALES & PURCHASES in D2 and match with columns G or H and will autonumbering and increment in D6 and copy under last row for cell in column G or H as in CR1,CR2,CR2 but CANCEL sheet should delete autonumbering in D6 when clear D2 and delete under last row for cell in column G when press commandbutton1(this case depends on CR3 sheet as in example)
thanks
Re: autonumbering for multiple columns based on two cells
Working with sheet "main" only
There are two cell D2 and D6
And the "Worksheet_change" event trigger those cells.
In D2: Pick item from Dropdown list. D6 and corresponding column G:J last row will generate new items with sequence number +1
In D6: Just Delete then the corresponding value in G:J will be deleted.
PHP Code:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim pos&, LastCell As Range, oldV, f If Target.Address(0, 0) = "D2" And Not IsEmpty(Target) Then pos = Evaluate("=match(D2,B2:B5,0)") Set LastCell = Range("F1").Offset(, pos).End(xlDown) Application.EnableEvents = False Range("D6").Value = Left(LastCell, Len(LastCell) - 3) & Format(Right(LastCell, 3) + 1, "000") Application.EnableEvents = True LastCell.Offset(1, 0).Value = Range("D6").Value ElseIf Target.Address(0, 0) = "D6" Then If IsEmpty(Target) Then Application.Undo oldV = Target Set f = Range("G2:J100").Find(oldV) If Not f Is Nothing Then f.ClearContents Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End If End Sub
2) Add Forms CommandButton, prefer on the worksheet rather than ActiveX object, in each sheet that you want the code to be executed.
Right click and assign "test" macro.
Re: autonumbering for multiple columns based on two cells
@jindion
perfect but you forgot last case in CANCEL sheet .
when clear D2,D6 ,then should clear last cell in column have ever autonumbering .
example : D2=SALES , when run macro D6=SS NO: S001 , but if I come back to cancelling by clear D2,D6 , then will clear S001 from SALES column .
Bookmarks