Hello,
I have created a "request form" - the form is not designed in VBA - it is simply a spreadsheet using Column B, only, and each row has either a "label" or a field to enter data: For example:
<Row 4> Project Code:
<Row 5> <Enter detail here>
<Row 6> Reason for Change>
<Row 7> <Enter reason here>
<Row 8> Deployment Date
<Row 9> <Date is entered here>
This spreadsheet is called "Deployment Request" (Source of data) (A)
The second spreadsheet (Destination for data captured) is called Request Report (B)
This spreadsheet is a lfat spreadsheet with columns mirroring the "labels"
Column A: Project A
Column B: Reason for Change
Column C: Deployment Date
I have also inserted a Command Button from the Form Control and associated it with the macro.
I would like people to fill in the form with their details and click on the "Submit" button.
This then transfers the required Rows from A to B
For every new request, a new row is added on B.
I really hope I make sense.
I have been reading through your previous posts / questions and tried using this:
Sub Update()
Dim faRow, x As Integer
The code below will only run when all fields contain data. Delete code lines 4-7 if you don't want this.
Also, column D on the second sheet will get stamped with todays date. Delete code line 14 if you don't want this.
Sub RunMe()
Dim lRow As Integer
If Range("B5") = vbNullString Or Range("B7") = vbNullString Or Range("B9") = vbNullString Then
MsgBox "Please enter all fields before submitting", vbCritical, "Incomplete data"
Exit Sub
End If
Range("B5,B7,B9").Copy
With Sheets("Request Report")
lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & lRow).PasteSpecial Transpose:=True
.Range("D" & lRow).Value = Date
End With
Application.CutCopyMode = False
End Sub
I had to remove the "full stop" before Range to make this work
lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
1. The data is being copied across
2. The form is recognising blank fields.
One pending issue - if you could kindly help me out...
Deployment Request - A
Report Request - B
When I add another request (even after closing down the file and re-opening the file) the previous request in B gets over-written - so there is always only one row in B.
Any chance we could accumulate the requests on the Request Report (B) - get Excel to add the new request to the next available row in Request Report?
So it's now
With Sheets("Request Report")
lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row + 1
.Range("A" & lRow).PasteSpecial Transpose:=True
.Range("D" & lRow).Value = Date
End With
It adds a second row
But third time round, it replaces the second row ....
If Range("B5") = vbNullString Or Range("B7") = vbNullString Or Range("B9") = vbNullString Then
MsgBox "Please enter all fields before submitting", vbCritical, "Incomplete data"
Exit Sub
End If
Range("B5,B7,B9").Copy
With ThisWorkbook.Sheets("Request Report")
nextRow = ThisWorkbook.Sheets("Request Report").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & nextRow).PasteSpecial Transpose:=True
.Range("D" & nextRow).Value = Date
End With
Application.CutCopyMode = False
End Sub
Using "With ThisWorkbook.Sheets"
It's all working now.
Thank you so much for all your help. You practically got me to a position where it was working and all I had to do was tweak it every so slightly.
Thanks for getting back to me. I am aware that we are able to copy data from one sheet to another and I would like to use the same concept. Only difference is: I need to somehow tell Excel to insert the data into the next row....
If we are able to copy data from sections into another sheet, by the use of a command button, then I think there could be a way out for me?
Thank you so much for your help.
I had to remove the "full stop" before Range to make this work
lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
1. The data is being copied across
2. The form is recognising blank fields.
One pending issue - if you could kindly help me out...
Deployment Request - A
Report Request - B
When I add another request (even after closing down the file and re-opening the file) the previous request in B gets over-written - so there is always only one row in B.
Any chance we could accumulate the requests on the Request Report (B) - get Excel to add the new request to the next available row in Request Report?
Kind Regards,
Naz
lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
So it's now
With Sheets("Request Report")
lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row + 1
.Range("A" & lRow).PasteSpecial Transpose:=True
.Range("D" & lRow).Value = Date
End With
It adds a second row
But third time round, it replaces the second row ....
I managed to fix the problem .... :)
Sub RunMe()
If Range("B5") = vbNullString Or Range("B7") = vbNullString Or Range("B9") = vbNullString Then
MsgBox "Please enter all fields before submitting", vbCritical, "Incomplete data"
Exit Sub
End If
Range("B5,B7,B9").Copy
With ThisWorkbook.Sheets("Request Report")
nextRow = ThisWorkbook.Sheets("Request Report").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & nextRow).PasteSpecial Transpose:=True
.Range("D" & nextRow).Value = Date
End With
Application.CutCopyMode = False
End Sub
Using "With ThisWorkbook.Sheets"
It's all working now.
Thank you so much for all your help. You practically got me to a position where it was working and all I had to do was tweak it every so slightly.
A huge thank you.
- Posts
- 2586
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 20, 2020
389