Automatically data to another sheet by adding new rows
Solved/Closed
NazCarr
TrowaD
- Posts
- 13
- Registration date
- Monday March 25, 2019
- Status
- Member
- Last seen
- January 26, 2021
TrowaD
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Related:
- Automatically data to another sheet by adding new rows
- Automatically transfer data from one sheet to another in excel - Guide
- Excel copy data from one sheet to another automatically ✓ - Forum - Excel
- How to automatically transfer save data from one sheet to another in excel - Guide
- Transfer data from one sheet to another automatically ✓ - Forum - Excel
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
2 replies
TrowaD
Mar 25, 2019 at 12:55 PM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Mar 25, 2019 at 12:55 PM
Hi Nazcarr,
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.
Best regards,
Trowa
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
Best regards,
Trowa
The operations you are asking about, is a database. Spreadsheets are for complex calculations, not storing data.
There is no coding involved with a database. Just entry form, and reports.
There is no coding involved with a database. Just entry form, and reports.
NazCarr
Mar 25, 2019 at 10:50 AM
- Posts
- 13
- Registration date
- Monday March 25, 2019
- Status
- Member
- Last seen
- January 26, 2021
Mar 25, 2019 at 10:50 AM
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?
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?
NazCarr
Mar 25, 2019 at 11:00 AM
- Posts
- 13
- Registration date
- Monday March 25, 2019
- Status
- Member
- Last seen
- January 26, 2021
Mar 25, 2019 at 11:00 AM
I am limited to the tools I can use.... I can only use and make the best of what I have - unfortunately :)
I have to admit that I am learning as I go... so any help would be greatly appreciated.
I have to admit that I am learning as I go... so any help would be greatly appreciated.
Mar 26, 2019 at 04:12 AM
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
Mar 26, 2019 at 05:51 AM
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 ....
Mar 26, 2019 at 06:33 AM
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.
Mar 26, 2019 at 12:33 PM