Previous Topic Next Topic

Automatically data to another sheet by adding new rows

Solved/Closed
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021 - Mar 25, 2019 at 10:03 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 26, 2019 at 12:33 PM
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

'x = ActiveCell.Row
Rows(x).Copy

faRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

Range("B" & x).Copy Sheets("Sheet2").Range("A" & faRow)
Range("D" & x).Copy Sheets("Sheet2").Range("B" & faRow)
Range("I" & x).Copy Sheets("Sheet2").Range("C" & faRow)

Sheets("Sheet2").Range("D" & faRow).Value = ActiveSheet.Name


End Sub

2 replies

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
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. 

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
1
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021
Mar 26, 2019 at 04:12 AM
Hi Trowa,
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
0
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021
Mar 26, 2019 at 05:51 AM
I added + 1 at the end of

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 ....
0
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021
Mar 26, 2019 at 06:33 AM
Hi Trowa,
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 26, 2019 at 12:33 PM
You are welcome NazCarr, glad I could help.
0
Answer 2 / 2
Blocked Profile
Mar 25, 2019 at 10:33 AM
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.
0
NazCarr 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?
0
Blocked Profile
Updated on Mar 25, 2019 at 10:56 AM
Well I am jist saying, the learning curve for making forms and reports in Access, is way less sharp than VBA.

Row.select
Selection.copy


Better yet, record a macro. Edit the macro to be dynamic by placing variables instead of the hard code the macro writes!
0
NazCarr 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.
0