How to create a macro assigned to the submit button on in the "interface" sheet
Category: [Excel] Demo Available
How to create a macro assigned to the submit button on in the "interface" sheet
Requirement:
The user requires a macro assigned to the submit button on in the "interface" sheet, the macro should vlookup the O11 cell selection and submit all the passengers data with the same vehicle e.g "NISSAN" into the "Central DB" table.
After submitting, all empty spaces left in the interface table should be remove and the table arranged with the leftover data.
After the user choses the NISSAN option and click the submit, all NISSAN data should be submitted to the Central DB sheet which has a similar table, after submitting, all the NISSAN info should be cleared from the Interface sheet table.
Solution:
Code:
Sub submit2temp()
Dim i As Integer
With Sheet1.ListObjects(1)
.ListRows.Add
With .ListRows(.ListRows.Count).Range
.Columns(1) = [o11]
For i = 2 To 7
.Columns(i) = .Parent.Cells(i + 11, 3)
Next
For i = 9 To 11
.Columns(i) = .Parent.Cells(i + 11, 3)
Next
.Columns(12) = .Parent.Cells(19, 3)
For i = 13 To 19
.Columns(i) = .Parent.Cells(i + 10, 3)
Next
End With
End With
[c13].Resize(18).ClearContents
Application.Goto [c13]
End Sub
and
Code:
Sub submit2main()
Dim x, y(), i As Long, ii As Long, iii As Long, iv As Integer
If [o11] <> "" Then
x = Sheet1.ListObjects(1).DataBodyRange
For i = 1 To UBound(x, 1)
If x(i, 1) = [o11] Then
ii = ii + 1: ReDim Preserve y(1 To UBound(x, 2), 1 To ii)
For iii = 1 To UBound(x, 2)
y(iii, ii) = x(i, iii)
Next
End If
Next
With Sheet6.ListObjects(1)
If .Parent.[a11] <> "" Then iv = .ListRows.Count
.Resize .Parent.[a10].Resize(iv + ii + 1, UBound(y, 1))
.ListRows(iv + 1).Range.Resize(UBound(y, 2)) = Application.Transpose(y)
End With
Application.ScreenUpdating = 0
With Sheet1.ListObjects(1)
For i = .ListRows.Count To 1 Step -1
If .ListRows(i).Range.Columns(1) = [o11] Then .ListRows(i).Delete
Next
End With
[o11] = ""
End If
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| Macro to insert new row at bottom of table, find highest value in column A and add 1 |
| How to protect cells |
| How to create VBA return that will return customised results when comparing two worksheets |
| How to unprotect command button |
| How to copy data from sheet 1 to sheet 2 |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.