Creating and using Forms in Excel

Question

Good day

I have created a form in excel using a recorded macro. The macro is as below:

Sub auto()

'

' auto Macro

'

'

Range("L4:L9").Select 

Selection.Copy 

Sheets("Rough").Select 

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks \_ 

    :=False, Transpose:=True 

Application.CutCopyMode = False 

Selection.Copy 

Sheets("Data").Select 

Range("C9").Select 

Selection.Insert Shift:=xlDown 

Sheets("Form2").Select 

Range("E5:G5,E7:G7,E9:G9,E11:G11,E13:G13,E15:G15").Select 

Range("E15").Activate 

Application.CutCopyMode = False 

Selection.ClearContents 

Range("E5:G5").Select 

End Sub

The resultant data is populated in the "Data" sheet as follows:

Deliverables (description)	Quantity/Rate (Y1)	Quantity/Rate (Y2)	Quantity/Rate (Y3)	Quantity/Rate (Y4)	Quantity/Rate (Y5)
Test3	0	0	0	0	0
Test2	0	0	0	0	0
Test1	0	0	0	0	0

I am trying to automatically get this information into a new sheet record by record as soon as a record is made available on this "Data" sheet by using an excel formula =IF(Data!$C$9="","",Data!$C$9). This formula brings the cell entry "Test1" to the new sheet. The problem arises when the second record is captured because the formula changes with the capturing of a captured record.

Answer 1

Could you share a test workbook?

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

What you want to do using macro?