Previous Topic Next Topic

Automatically data to another sheet by adding new rows

Solved/Closed
NazCarr Posts 12 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 December 27, 2022 - 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 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
NazCarr Posts 12 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
NazCarr Posts 12 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 ....
NazCarr Posts 12 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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 26, 2019 at 12:33 PM
You are welcome NazCarr, glad I could help.
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.
NazCarr Posts 12 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?
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!
NazCarr Posts 12 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.

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
Ig stories from close friends to public
Darren -
zara -

31 responses
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses
Why does our account keep randomly following additional users every few weeks?
Coach -
ElenaKM -

1 response