Automatically data to another sheet by adding new rows [Solved]

Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
- - Latest reply: TrowaD
Posts
2538
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 17, 2019
- 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

See more 

2 replies

Best answer
Posts
2538
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 17, 2019
369
1
Thank you
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

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6261 users have said thank you to us this month

NazCarr
Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
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
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
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
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
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
2538
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 17, 2019
369 -
You are welcome NazCarr, glad I could help.
Respond to TrowaD
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1473
0
Thank you
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
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
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?
ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1473 -
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
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
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.
Respond to ac3mark