Auto serial number VBA code

[Zubair]

Hi Experts.

I have the following 2 sheets, need VBA code to get the Document number automatically and data manual input, A5 to G manual input.

Want to move transactions from the Entry to Data tab and clear all data in the Entry sheet.

Entry

Document No.
Date
Product	Size	Quantity	Rate	Amount
Black	13​	40​	3​	120​
Pink	15​	30​	5​	150​
Blue	13​	50​	4​	200​
Green	12​	80​	8​	640​

Data

Document No.	Date	Product	Size	Quantity	Rate	Amount
10001​	1/1/2022​	Black	13​	40​	3​	120​
10001​	1/1/2022​	Pink	15​	30​	5​	150​
10001​	1/1/2022​	Blue	13​	50​	4​	200​
10001​	1/1/2022​	Green	12​	80​	8​	640​
10002​	7/1/2022​	Blue	20​	10​	4​	40​
10002​	7/1/2022​	Black	15​	12​	3​	36​
10003​	22/1/2022	Pink	15​	30​	5​	150​
10003​	22/1/2022	Blue	13​	50​	4​	200​
10003​	22/1/2022	Green	12​	80​	8​	640​

 

[Zubair]

please help

 

[footoo]

This could probably be done with Excel's built-in Input Form but here's some VBA.
Put this in the Entry sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Application.EnableEvents = False
With Sheets("Data").Cells(Rows.Count, "A").End(3)
    If .Value = "Document No." Then
        [B1] = 100001
    Else: [B1] = .Value + 1
    End If
End With
Application.EnableEvents = True
End Sub

Put this in a normal module :

Sub v()
Dim rng As Range, r%
Set rng = Range([A5], Cells(Rows.Count, "E").End(3))
r = rng.Rows.Count
With Sheets("Data").Cells(Rows.Count, "C").End(3)
    rng.Copy .Offset(1)
    [B1].Copy .Offset(1, -2).Resize(r)
    [B2].Copy .Offset(1, -1).Resize(r)
End With
Application.EnableEvents = False
Union([B1:B2], rng).ClearContents
Application.EnableEvents = True
End Sub

Do not enter the Document No. in B1 of the Entry sheet.
When you enter the date in B2 the Document No. will appear.

When you have completed the data on the Entry sheet, with the Entry sheet active run the macro "v".

 

[Zubair]

footoo, perfect! many thanks