Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
 -
Posts
2647
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 3, 2020
 -
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

Answer 1 / 2
Posts
2647
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 3, 2020
 435
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
Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2803 users have said thank you to us this month

Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
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
Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
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 ....
Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
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.
Report
Posts
2647
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 3, 2020
 435
You are welcome NazCarr, glad I could help.
Answer 2 / 2

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.
Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
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?
Report
Blocked Profile
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!
Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
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.
Ask a question